Skip navigation links

MySQL Forums :: Newbie :: Difficulty framing a JOIN statement


Advanced Search

Re: Difficulty framing a JOIN statement
Posted by: laptop alias ()
Date: April 16, 2012 12:28AM

Erm, on the basis that ps.roleno can be NULL while ps.personid cannot, that IS what my query does!

Oh and PB meant 'FROM' instead of 'JOIN'
See A.

--
Frequent responses

A. Read the Stickies at the top of the forum. Sometimes they disappear, in which case...

If your question concerns tables and data:

Within [ code ][ /code ] tags (leave off the spaces)...
1. Provide CREATE TABLE statements for each of the relevant tables
2. Provide a small but representative dataset for each of the tables, as a set of INSERT statements
3. Provide the resultset you'd expect from your query.
4. Provide the result of SELECT VERSION();

B. Group-wise Max queries
One frequently asked question relates to finding the Top-N of an aggregated result, one of a set of so-called 'GROUP-WISE MAX' queries.
Various solutions to this problem are provided at the following links:
dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
www.artfulsoftware.com/infotree/queries.php?&bw=1179#101
jan.kneschke.de/projects/mysql/groupwise-max

C. Pivot Tables
A second frequently asked question involves the transposition of rows to columns, often involving the aggregation of data, so-called 'Pivot Table' queries. Although my preferred solution is to handle the transposition at the application level, e.g. with a bit of PHP, there is excellent discussion on this topic at www.artfulsoftware.com/infotree/queries.php?&bw=1179#523.

D. Hierarchical Data
A third frequently asked question relates to handling recursion in connection with hierarchical data.
Choices include, but are not limited to, the following:
- Join the table to itself as many times as could possibly be required
- Handle the recursion at the application level, e.g. with a PHP loop <-- my preference
- Use a Stored Procedure to handle the recursion
- Switch to a Nested Set (or some sort of hybrid model) instead of the 'so-called' Adjacency List model

All of these solutions are discussed more thoroughly elsewhere, including a number of excellent, popular, and hence easily googled, articles on Hierarchical data and MySQL.



Edited 1 time(s). Last edit at 04/16/2012 12:31AM by laptop alias.

Options: ReplyQuote


Subject Written By Posted
Difficulty framing a JOIN statement tony mount 04/15/2012 05:45AM
Re: Difficulty framing a JOIN statement laptop alias 04/15/2012 06:25AM
Re: Difficulty framing a JOIN statement tony mount 04/15/2012 06:58AM
Re: Difficulty framing a JOIN statement tony mount 04/15/2012 07:01AM
Re: Difficulty framing a JOIN statement laptop alias 04/15/2012 07:42AM
Re: Difficulty framing a JOIN statement tony mount 04/15/2012 02:02PM
Re: Difficulty framing a JOIN statement Peter Brawley 04/15/2012 02:18PM
Re: Difficulty framing a JOIN statement tony mount 04/16/2012 12:18AM
Re: Difficulty framing a JOIN statement laptop alias 04/16/2012 12:28AM
Re: Difficulty framing a JOIN statement -solved tony mount 04/16/2012 12:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.