MySQL join two separate query results

Question

I've two different queries like

// This query will return past month records
SELECT b.list_id, r.name, r.city, FROM_UNIXTIME(b.bookingdate, '%Y-%m-%d- %h:%i:%s') AS 'bookingdate', COUNT(*) AS bookings
FROM booking b JOIN restaurants r ON r.list_id = b.list_id
WHERE b.bookingdate BETWEEN 1383224400 AND 1385730000 GROUP BY b.list_id

// This query will return current month records
SELECT b.list_id, r.name, r.city, FROM_UNIXTIME(b.bookingdate, '%Y-%m-%d- %h:%i:%s') AS 'bookingdate', COUNT(*) AS bookings
FROM booking b JOIN restaurants r ON r.list_id = b.list_id
WHERE b.bookingdate BETWEEN 1385816400 AND 1388408400 GROUP BY b.list_id

Result fields for both queries list_id, name, city, bookingdate, bookings

I want to combine both query results into one and want result fields like list_id, name, city, bookingdate, bookings_lastmonth, bookings_currentmonth

What is the best way to do this?

JOIN has special meaning in SQL, using that in your title makes this confusing. — Barmar
– Barmar, Commented Dec 10, 2013 at 5:39

Suleman Ahmad · Accepted Answer · 2013-12-10 05:50:43Z

3

Use UNION property:-

SELECT b.list_id, r.name, r.city, FROM_UNIXTIME(b.bookingdate, '%Y-%m-%d- %h:%i:%s') AS 'bookingpreviousdate', '0' AS bookingnextdate, COUNT(*) AS bookings
FROM booking b JOIN restaurants r ON r.list_id = b.list_id
WHERE b.bookingdate BETWEEN 1383224400 AND 1385730000 GROUP BY b.list_id
UNION
SELECT b.list_id, r.name, r.city, '0' AS 'bookingpreviousdate', 'FROM_UNIXTIME(b.bookingdate, '%Y-%m-%d- %h:%i:%s')' AS bookingnextdate, COUNT(*) AS bookings
FROM booking b JOIN restaurants r ON r.list_id = b.list_id
WHERE b.bookingdate BETWEEN 1385816400 AND 1388408400 GROUP BY b.list_id

edited Dec 10, 2013 at 5:50

answered Dec 10, 2013 at 5:37

Suleman Ahmad

2,1235 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nawaz Over a year ago

I want to combine both query results into one and want result fields like list_id, name, city, bookingdate, bookings_lastmonth, bookings_currentmonth UNION will give results of same fields like list_id, name, city, bookingdate, bookings inshort, I want result field "bookings" in two different columns bookings_lastmonth, bookings_currentmonth

Suleman Ahmad Over a year ago

then add one more column for dates.

Suleman Ahmad Over a year ago

for 'previousmonthdates' value will be not equal to 0, and for 'nextmonthdates' value will be not equal to 0.

Kaleidoscope · Accepted Answer · 2013-12-10 05:36:58Z

0

use the union operator, it'll put two queries into one table

http://dev.mysql.com/doc/refman/5.0/en/union.html

answered Dec 10, 2013 at 5:36

Kaleidoscope

3,6271 gold badge28 silver badges21 bronze badges

Comments

Imre L · Accepted Answer · 2013-12-10 06:26:15Z

0

I am not sure about the bookingdate

SELECT b.list_id, r.name, r.city, FROM_UNIXTIME(b.bookingdate, '%Y-%m-%d- %h:%i:%s') AS bookingdate, 
  COUNT(b.list_id) AS bookings_lastmonth, COUNT(c.list_id) AS bookings_currentmonth
FROM restaurants r 
LEFT JOIN booking b ON r.list_id = b.list_id AND b.bookingdate BETWEEN 1383224400 AND 1385730000 
LEFT JOIN booking c ON r.list_id = c.list_id AND c.bookingdate BETWEEN 1385816400 AND 1388408400
GROUP BY r.list_id
HAVING bookings_lastmonth>0 OR bookings_currentmonth>0

answered Dec 10, 2013 at 6:26

Imre L

6,29126 silver badges32 bronze badges

Comments

Nawaz · Accepted Answer · 2013-12-10 08:33:12Z

Somehow its worked by this query

SELECT t1.list_id, t1.name, t1.city, t1.bookings AS bookings_lastmonth, t2.bookings AS bookings_currentmonth 
FROM   (SELECT b.list_id, r.name, r.city, FROM_UNIXTIME(b.bookingdate, '%Y-%m-%d- %h:%i:%s') AS 'bookingdate', COUNT(*) AS bookings 
        FROM   booking b JOIN restaurants r ON r.list_id = b.list_id 
        WHERE  b.bookingdate BETWEEN 1383224400 AND 1385730000 
        GROUP  BY b.list_id) AS t1

        JOIN (SELECT b.list_id, r.name, r.city, FROM_UNIXTIME(b.bookingdate, '%Y-%m-%d- %h:%i:%s') AS 'bookingdate', COUNT(*) AS bookings 
        FROM   booking b JOIN restaurants r 
        ON r.list_id = b.list_id 
        WHERE  b.bookingdate BETWEEN 1385816400 AND 1388408400 
        GROUP  BY b.list_id) AS t2 

        ON t1.list_id = t2.list_id

but still I have to work for more imporovements

Collectives™ on Stack Overflow

MySQL join two separate query results

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related