Lachesis Lachesis - 2 months ago 7
MySQL Question

SQL: join select multiple tables with missing row

I have a big multiple table query join select where some values are optional.

This is the query:

SELECT a.date_in, a.date_out, b.name, b.phone, b.birthdate,
b.country, b.hotel, b.room_nr, b.passport_nr, c.email,
d.size, e.name, GROUP_CONCAT(DISTINCT g.service), GROUP_CONCAT(DISTINCT h.service)
, GROUP_CONCAT(DISTINCT i.time), GROUP_CONCAT(DISTINCT j.location)
FROM reservation a, rider b, user c,
bike_size d, bike e, services_reservation f, services g,
bike_shipping h, bike_shipping_reservation i
, bike_shipping_location j WHERE a.rider_id = b.id
AND b.user_id = c.id AND a.bike_size_id = d.id AND
d.bike_id = e.id AND a.id = f.reservation_id AND
f.services_id = g.id
AND h.id = i.bike_shipping_id AND a.id = i.reservation_id
AND i.bike_shipping_location_id = j.id
AND a.id = 80


In the tables from the query above, the table named
services_reservation
with the following columns (
id
,
services_id
,
reservation_id
) is completely empty in this case, which makes the values that I select from the table
bike_shipping_reservation
NULL.

How can I make some tables that I select from optional in case they are empty?

Here is the SQL Fiddle with 1 empty table, you can see the NULL results at the end (only GROUP_CONCAT(DISTINCT g.service) should be NULL).

http://sqlfiddle.com/#!9/ee31b/6

Here is the SQL Fiddle with all tables having values in there columns, you can see that all values are returned not NULL.

http://sqlfiddle.com/#!9/8bc033/34

Any thoughts?

Answer

Use left join where the table (or the row) are empty on don't match

SELECT a.date_in, a.date_out, b.name, b.phone, b.birthdate,
b.country, b.hotel, b.room_nr, b.passport_nr, c.email,
d.size, e.name, GROUP_CONCAT(DISTINCT g.service), GROUP_CONCAT(DISTINCT     h.service)
, GROUP_CONCAT(DISTINCT i.time), GROUP_CONCAT(DISTINCT j.location)

FROM reservation a 
INNER JOIN rider b on  a.rider_id = b.id 
INNER JOIN user c on b.user_id = c.id  
INNER JOIN bike_size d on a.bike_size_id = d.id 
INNER JOIN bike e ON d.bike_id = e.id  
LEFT  JOIN services_reservation f on  a.id = f.reservation_id   
INNER JOIN services g on f.services_id = g.id
INNER JOIN bike_shipping_reservation i on a.id = i.reservation_id
INNER JOIN bike_shipping h ON h.id = i.bike_shipping_id
INNER JOIN bike_shipping_location j on i.bike_shipping_location_id = j.id
where a.id = 80 
Comments