It is a mySQL UNION "problem" referenced in a couple of places on the web.
Basically, the topmost query must not select NULL, and must select character
placeholders for the largest possible piece of data that may be present in each
column, and also select zero for number columns. What is unfortunate about
this, is the topmost query therefore needs to have intimate knowledge of the
size of each varchar(), or else it won't pad the topmost query enough.
For example, this query won't work in mySQL as it does with other DB's:
(SELECT 1 AS Tag,
NULL AS Parent,
Users.UserId AS 'User!1!UserID!hide',
Users.FirstName AS 'User!1!FirstName',
Users.LastName AS 'User!1!LastName',
NULL AS 'Cars!2!CarID!hide',
NULL AS 'Cars!2!Details',
NULL AS 'Trips!3!TripID!hide',
NULL AS 'Trips!3!Mileage'
FROM Users
WHERE Users.UserId='2')
UNION ALL
(SELECT 2,
1,
Users.UserId,
NULL,
NULL,
Cars.CarID,
Cars.Details,
NULL,
NULL
FROM Cars
INNER JOIN Users ON Cars.UserID = Users.UserID
WHERE Users.UserId='2')
UNION ALL
(SELECT 3,
2,
Users.UserId,
NULL,
NULL,
Cars.CarID,
NULL,
Trips.TripID,
Trips.Mileage
FROM Trips
INNER JOIN Cars ON Trips.CarID = Cars.CarID
INNER JOIN Users ON Cars.UserID = Users.UserID
WHERE Users.UserId='2')
ORDER BY 'Cars!2!CarID!hide',
'User!1!UserID!hide',
Tag
;
*** BUT, this query, adjusted by using "blanks" and zeroes in the topmost
query, will work:
SELECT 1 AS Tag,
0 AS Parent,
Users.UserId AS 'User!1!UserID!hide',
concat(Users.FirstName, ' ') AS 'User!1!FirstName',
concat(Users.LastName, ' ') AS 'User!1!LastName',
0 AS 'Cars!2!CarID!hide',
' ' AS
'Cars!2!Details',
0 AS 'Trips!3!TripID!hide',
0 AS 'Trips!3!Mileage'
FROM Users
WHERE Users.UserId='2'
UNION ALL
SELECT 2,
1,
Users.UserId,
'',
'',
Cars.CarID,
Cars.Details,
0,
0
FROM Cars
INNER JOIN Users ON Cars.UserID = Users.UserID
WHERE Users.UserId='2'
UNION ALL
SELECT 3,
2,
Users.UserId,
'',
'',
Cars.CarID,
'',
Trips.TripID,
Trips.Mileage
FROM Trips
INNER JOIN Cars ON Trips.CarID = Cars.CarID
INNER JOIN Users ON Cars.UserID = Users.UserID
WHERE Users.UserId='2'
ORDER BY 'Cars!2!CarID!hide',
'User!1!UserID!hide',
Tag
;
This example references these setup scripts:
http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:135092
-Mike
__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
