AndroidNewBee AndroidNewBee - 1 month ago 7
SQL Question

How to Join two separate MySQL queries to give one result?

I have two queries which I would like to merge to give a single output.
This is the first query:-

SELECT `booking_request`.`booking_id`,`booking_type`,`from_date`,`to_date`,`city`,`pickUp_point`,`drop_point`,`reporting_time` FROM `booking_request`
JOIN `tour_chart` ON
`booking_request`.`booking_id` = `tour_chart`.`booking_id`
WHERE `tour_chart`.`driver_id` = '2';


This is the 2nd query:-

SELECT `guest_name`,`guest_mobile` FROM `guest_info`
JOIN `guest_booking_table` ON
`guest_info`.`guest_id` = `guest_booking_table`.`guest_id`
JOIN `tour_chart` ON
`guest_booking_table`.`booking_id` = `tour_chart`.`booking_id`
WHERE `tour_chart`.`driver_id` = '$driver_id';


I have tried doing something like:-

SELECT * FROM
(SELECT `tour_chart`.`driver_id`,`booking_request`.`booking_id`,`booking_type`,`from_date`,`to_date`,`city`,`pickUp_point`,`drop_point`,`reporting_time` FROM `booking_request`
JOIN `tour_chart` ON
`booking_request`.`booking_id` = `tour_chart`.`booking_id`
WHERE `tour_chart`.`driver_id` = '2') x INNER JOIN
(SELECT `tour_chart`.`driver_id`,`guest_name`,`guest_mobile` FROM `guest_info`
JOIN `guest_booking_table` ON
`guest_info`.`guest_id` = `guest_booking_table`.`guest_id`
JOIN `tour_chart` ON
`guest_booking_table`.`booking_id` = `tour_chart`.`booking_id`
WHERE `tour_chart`.`driver_id` = '2') y
ON x.`tour_chart`.`driver_id` = y.`tour_chart`.`driver_id`;


How can I merge these two queries into a single query which gives a result containing booking_id,booking_type,from_date,to_date,city,pickUp_point,drop_point,reporting_time,guest_name and guest_mobile.
Any help or suggestion is appreciated.
Thank you.

Answer

I think you can just combine all the joins into a single query:

SELECT br.booking_id, bt.booking_type,
       from_date, to_date, city,
       pickUp_point, drop_point, reporting_time,
       gi.guest_name, gi.guest_mobile
FROM booking_request br JOIN
     tour_chart tc
     ON br.booking_id = tc.booking_id JOIN
     guest_booking_table gbt
     ON gbt.booking_id = tc.booking_id JOIN
     guest_info gi
     ON gi.guest_id = gbt.guest_id
WHERE tc.driver_id = 2;

Notes:

  • Table aliases make the query easier to write and to read, so use them.
  • Backticks make the query harder to write and to read, so don't use them.
  • Qualify all column names (that is, include the table alias). You could get ambiguous column names without doing this.
  • Only use single quotes for string and date constants. I assume that driver_id is a number, so I removed the single quote.