Hello MySQL Guru's-
This is regarding UNION support in MySQL 4.0.12.
The query below is attempting to return rows that can
be easily parsed for the purposes of creating XML.
The ORDER BY is not being processed properly at the
end of the query. Sample scripts are shown below, to
setup the DB and test the query.
The query should result in a heirarchy of rows, so
that the "Tag" column should have a sequence of:
1,2,3,3,3,3,2,3,2,3,3,3
MySQL instead returns an unsorted list:
1,2,2,2,3,3,3,3,3,3,3,3
Does anyone know how to get the UNION to actually
ORDER BY properly?
------------------------
Here is DB setup script:
------------------------
CREATE DATABASE UnionTestDB;
USE UnionTestDB;
CREATE TABLE Users (
UserID int NOT NULL ,
FirstName varchar (32) NOT NULL ,
LastName varchar (32) NOT NULL
)
;
CREATE TABLE Cars (
CarID int NOT NULL ,
UserID int NOT NULL ,
Details varchar (64) NULL
)
;
CREATE TABLE Trips (
TripID int NOT NULL ,
CarID int NOT NULL ,
Mileage int NULL
)
;
------------------------
Here is Init DB script:
------------------------
INSERT INTO Cars (CarID, UserID, Details) VALUES (1,
2, 'Ford');
INSERT INTO Cars (CarID, UserID, Details) VALUES (2,
2, 'Toyota');
INSERT INTO Cars (CarID, UserID, Details) VALUES (3,
2, 'Honda');
INSERT INTO Trips (TripID, CarID, Mileage) VALUES (1,
1, 123);
INSERT INTO Trips (TripID, CarID, Mileage) VALUES (2,
1, 450);
INSERT INTO Trips (TripID, CarID, Mileage) VALUES (3,
1, 798);
INSERT INTO Trips (TripID, CarID, Mileage) VALUES (4,
1, 27);
INSERT INTO Trips (TripID, CarID, Mileage) VALUES (5,
2, 58);
INSERT INTO Trips (TripID, CarID, Mileage) VALUES (6,
3, 1200);
INSERT INTO Trips (TripID, CarID, Mileage) VALUES (7,
3, 1222);
INSERT INTO Trips (TripID, CarID, Mileage) VALUES (8,
3, 377);
INSERT INTO Users (UserID, FirstName, LastName) VALUES
(1, 'Ted', 'Smith');
INSERT INTO Users (UserID, FirstName, LastName) VALUES
(2, 'Sally', 'Jackson');
INSERT INTO Users (UserID, FirstName, LastName) VALUES
(3, 'Meg', 'Ryan');
INSERT INTO Users (UserID, FirstName, LastName) VALUES
(4, 'Tony', 'Curtis');
-------------------------------------
Finally, here is the UNION ALL query:
-------------------------------------
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
;
__________________________________________________
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
