"Shaun" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Hi,
The following 3 queries on their own produce more rows than if I UNION
them
together:
SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('W_', Work_Type_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Booking'
UNION
SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('T_', Task_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Task'
UNION
SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('U_', Unavailability_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Unavailability'
I am trying to get all types of bookings - unavailability, tasks and
bookings into one result but am confused as to why the query produces less
rows this way.
Any tips here would be greatly appreciated.
UNION removes duplicate rows from the result set; in other words, if two
identical rows are produced by two or more of the queries that have been
UNIONed together, the duplicate rows are removed from the final result set.
If you don't want to remove the duplicates, use UNION ALL instead of UNION.
(I'm not sure if UNION ALL is supported in MySQL but it certainly is in DB2,
my main database.)
Perhaps that's why you have fewer rows in the UNION result than you do by
summing up the row counts in the individual queries?
By the way, I'm not sure how you posted your question but my copy of Outlook
Express thinks it's a newsgroup post, not an email so I couldn't reply in
the normal way.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/271 - Release Date: 28/02/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
