SQL: join select multiple tables with missing row

Question

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?

Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax with the conditions in the ON clause. — Gordon Linoff
– Gordon Linoff, Commented Sep 10, 2016 at 17:15
As @GordonLinoff already stated, use explicit JOIN syntax which will allow you to specify behaviour of the combination (LEFT, INNER ...). You will most likely achieve your result using side joins where appropriate. — Kamil Gosciminski
– Kamil Gosciminski, Commented Sep 10, 2016 at 17:23
@Lachesis . . . Not directly. But it will make it you query easier to understand and hence make it easier for others to help you. — Gordon Linoff
– Gordon Linoff, Commented Sep 10, 2016 at 17:25

ScaisEdge · Accepted Answer · 2016-09-10 17:30:47Z

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

Collectives™ on Stack Overflow

SQL: join select multiple tables with missing row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related