SQL not returning values where null in another table

I’m trying to use the SQL join function to grab information from multiple tables.

My issue is I can’t seem to get the desired result.

     select a.DRINKER, sum(C.PRICE)
     from DRINKERS a
     left join ORDERS b on a.DRINKER = b.DRINKER
     join SERVES c on b.PUB = c.PUB and d.DRINK = c.DRINK
     group by a.DRINKER;

This gives the following results

----------------------
|DRINKER|sum(C.PRICE)|
----------------------
|  BOB  |   200.10   |
|  NED  |   172.50   |
|  JOE  |   270.90   |
|  TIM  |    80.10   |
----------------------

However I want this to be giving all of the people in a.DRINKER like such:

----------------------
|DRINKER|sum(C.PRICE)|
----------------------
|  BOB  |   200.10   |
|  NED  |   172.50   |
|  JOE  |   270.90   |
|  TIM  |    80.10   |
|  PAT  |    null    |
|  ANN  |    null    |
----------------------

Any guidance would be appreciated and if you could also explain the logic behind the changes that would be greatly appreciated as I wanna learn what I should be doing! Thanks in advance!

Answer

Even though you got a left join between DRINKERS and ORDERS, the join between ORDERS and SERVES, will filter out any nulls obtained in the first left join.

To fix this you could try by further left joining the tables

 select a.DRINKER, sum(C.PRICE)
   from DRINKERS a
   left join ORDERS b on a.DRINKER = b.DRINKER
   left join SERVES c on b.PUB = c.PUB and d.DRINK = c.DRINK
  group by a.DRINKER;

Advertisement

Answer