MySQL LEFT JOIN tables or PHP combine arrays

Question

I'm stuck and I would like your help (advice). I'm using PHP & MysQL and my issue is presented down below.

This is a simplified version of what I'm trying to achieve. I have two tables, one is city list, and the other is departures. I would like to LEFT JOIN these two tables (if possible) and return list of all cities even if there is no connected records in table bus_departures.

Can I do it using just MySQL or I'll have to combine arrays in PHP. I would like to see your solution. Thank you.

City

-------------------     
ID | CITY               
-------------------     
1   | London            
2   | Paris             
3   | New York          
4   | Rome              
5   | Zagreb

Bus_Departures

------------------------------------------
ID  | DATE          | CITY          | No. DEPARTURES
------------------------------------------
1   | 2016-06-06    | 1             | 5
2   | 2016-06-06    | 4             | 3
3   | 2016-06-06    | 3             | 2
4   | 2016-06-07    | 3             | 4
5   | 2016-06-07    | 1             | 1
6   | 2016-06-08    | 3             | 8
7   | 2016-06-09    | 1             | 3
8   | 2016-06-09    | 4             | 2

SELECT

SELECT * FROM City LEFT JOIN Bus_Departures WHERE date = '2016-06-07' ORDER BY City ASC

RETURNS

London      2016-06-07      1
New York    2016-06-07      4

I WOULD LIKE TO ECHO THIS TO THE USER

London      2016-06-07      1
New York    2016-06-07      4
Paris       2016-06-07      - (no records at all)
Rome        2016-06-07      - (no records for this day)
Zagreb      2016-06-07      - (no records at all)

Thank you!

You have made it extremely difficult for anyone to copy paste your data/tables. Please post them as two separate sections instead of side by side. Also post numbers instead of 1 (london) etc. We can figure that out thank you — e4c5
– e4c5, Commented Oct 6, 2016 at 12:33
The left join is okay, but your WHERE statement is on the right-side table, after the left join has been performed. As a result, for example your joined Paris entry would look like Paris NULL NULL and not be selected, just like the two Rome entries that will look like Rome 2016-06-06 3 and Rome 2016-06-09 2. — Elijan9
– Elijan9, Commented Oct 6, 2016 at 13:58
thanks for the critique e4c5, I will try to post more user friendly tables next time! thank you Elijan9 once again. — kiflica
– kiflica, Commented Oct 7, 2016 at 4:59

Elijan9 · Accepted Answer · 2016-10-06 15:42:53Z

2

You certainly need to do a LEFT JOIN for your use-case, but in your current statement, your WHERE statement will run on the already joined table. So right before the WHERE clause is used, the already joined table will look like:

London            | 2016-06-06  | 5
Rome              | 2016-06-06  | 3
New York          | 2016-06-06  | 2
New York          | 2016-06-07  | 4
London            | 2016-06-07  | 1
New York          | 2016-06-08  | 8
London            | 2016-06-09  | 3
Rome              | 2016-06-09  | 2
Paris             | NULL        | NULL
Zagreb            | NULL        | NULL

Now it is obvious that a WHERE date = '2016-06-07' is not going to return the data you want.

It will however work if you include your date requirement in the join clause, for example:

SELECT * FROM City LEFT JOIN Bus_Departures
  ON (City.ID = Bus_Departure.CITY AND Bus_Departure.date = '2016-06-07')
  ORDER BY City ASC

In that case, the LEFT JOIN is performed with just this on the right side:

------------------------------------------
ID  | DATE          | CITY          | DEPARTURES
------------------------------------------
4   | 2016-06-07    | 3             | 4
5   | 2016-06-07    | 1             | 1

(It will also run faster.)

Since you also want the date included in your result, the complete statement could look like:

SELECT City.CITY, '2016-06-07', Bus_Departure.DEPARTURES 
  FROM City LEFT JOIN Bus_Departures
  ON (City.ID = Bus_Departure.CITY AND Bus_Departure.date = '2016-06-07')
  ORDER BY City.ID ASC

edited Oct 6, 2016 at 15:42

answered Oct 6, 2016 at 15:37

Elijan9

1,3142 gold badges18 silver badges19 bronze badges

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

1 Comment

kiflica Over a year ago

You have helped me big time Elijan9, thank you! This is EXACTLY what I needed and you even explained it step-by-step! thank you

Alexandru Muscalu · Accepted Answer · 2016-10-06 14:35:01Z

0

When you do the where clause you restrict by the date you get rid of all the null values. If you want to still see them in your result you need to include the null check in your query.

select c.CITY, bd.DATE, bd.DEPARTURES from city c
left join bus_departures as bd on bd.city = c.id
where bd.DATE='2016-06-07' or bd.DATE is null;

answered Oct 6, 2016 at 14:35

Alexandru Muscalu

1

1 Comment

Elijan9 Over a year ago

That won't work if a city has entries in Departures, just not on the requested date (e.g. Rome in the example of the topic starter)

Collectives™ on Stack Overflow

MySQL LEFT JOIN tables or PHP combine arrays

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related