Writing UNION statements in MySQL 3.x - Overview
(Page 2 of 4 )
The secret lies in the use of LEFT JOIN's and a dummy table. A LEFT JOIN is similar to an INNER JOIN (which is the standard type of join), except that even if the join requirements aren't met, a record is returned, but the fields from the joined table will be NULL.
The dummy table is a table that must be created in the database with only one field. I call the table _dummy and I call the field num. The table simply contains a different number in each record, starting at zero. You only need as many records as UNION's you plan to use. For example, if you only need to join two SELECT's, you only need 0 and 1 in _dummy. If you plan to join four SELECT's, then you need 0 through 3. This dummy table will allow you to make separate queries within one query.
Here's the basic overview of how it's done:
SELECT [fields]
FROM _dummy AS D
LEFT JOIN [table1] ON (D.num = 0 AND [condition1])
LEFT JOIN [table2] ON (D.num = 1 AND [condition2])
.
.
.
WHERE D.num < [table count] |
Next: The Details >>
More Database Articles Articles
More By Codewalkers
