List:General Discussion« Previous MessageNext Message »
From:Mike Lemke Date:March 20 2003 6:44pm
Subject:Re: UNION operates incorrectly
View as plain text  
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
Thread
UNION operates incorrectlyMike Lemke20 Mar
  • Re: UNION operates incorrectlyFred van Engen20 Mar
    • Re: UNION operates incorrectlyDan Nelson20 Mar
      • Re: UNION operates incorrectlyFred van Engen20 Mar
    • Re: UNION operates incorrectlyMike Lemke20 Mar