This my Query
SELECT COUNT(*) as total, toys, date FROM T1
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
UNION
SELECT COUNT(*) as total, toys, date FROM T2
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
UNION
SELECT COUNT(*) as total, toys, date FROM T3
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType
HAVING COUNT( total ) = 4
Output result
count Toys date
3 Bibi 2012-06-26
4 Baba 2012-06-26
How can i get MYSQL to show results only for count=4
SELECT * FROM (
SELECT COUNT(*) as total, toys, date FROM T1
WHERE (date >= '2012-06-26' AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType
UNION
SELECT COUNT(*) as total, toys, date FROM T2
WHERE (date >= '2012-06-26' AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType
UNION
SELECT COUNT(*) as total, toys, date FROM T3
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType) AS src
WHERE total = 4;
Please, note, that for proper data groupping you must have all columns either in the GROUP BY
clause or as arguments to the aggregate functions. It is MySQL feature to allow you to avoid this restriction, but it might lead you to the unexpected results.