Join error and order by

Question

I'm trying to write a query which does the below:

For every guest who has the word “Edinburgh” in their address show the total number of nights booked. Be sure to include 0 for those guests who have never had a booking. Show last name, first name, address and number of nights. Order by last name then first name.

I am having problems with making the join work properly, ER Diagram Snippet:

Here is my current (broken) solution:

SELECT last_name, first_name, address, nights
FROM booking
RIGHT JOIN guest ON (booking.booking_id = guest.id)
WHERE address LIKE '%Edinburgh%';

Here is the results from that query:

The query is partially complete, hoping someone can help me out and create a working version. I'm currently in the process of learning SQL so apologies if its a rather basic or dumb question!

Are you sure that we should be doing your homework? – Strawberry Nov 07 '16 at 00:12 — Strawberry, Nov 07 '16 at 00:12

Kamil Gosciminski · Accepted Answer · 2016-11-06T22:54:35.327

1

Your query seems almost correct. You were joining the booking id with guets id which gave you some results because of overlapping (matching) ids, but this most likely doesn't correspond to the foreign keys. You should join on guest_id from booking to id from guest.

I'd add grouping to sum all booked nights for a particular guest (assuming that nights is an integer):

SELECT g.last_name, g.first_name, g.address, SUM(b.nights) AS nights
FROM guest AS g
LEFT JOIN booking AS b ON b.guest_id = g.id
WHERE g.address LIKE '%Edinburgh%'
GROUP BY g.last_name, g.first_name, g.address;

Are you sure that nights spent should be calculated using nights field? Why can it be null? If you'd like to show zero for null values just wrap it up with a coalesce function like that:

COALESCE(SUM(b.nights), 0)

Notes:

Rewriten RIGHT JOIN into LEFT JOIN, but that doesn't affect results - it's just cleaner for me
Using aliases eg. AS g makes the code shorter when specifying joining columns
Reference every column with their table alias to avoid ambiguity

edited Nov 06 '16 at 22:54

answered Nov 06 '16 at 21:46

Kamil Gosciminski

16,547
8
49
72

Great thanks for your explaination. I'll use aliases from now on. – Aidan Nov 06 '16 at 21:50
`b.booking_id = g.id` - really? – Paul Spiegel Nov 06 '16 at 22:32
@PaulSpiegel I did not verify the joining logic. Thanks for pointing out. Answer updated accordingly. This seems to be causing the problem. – Kamil Gosciminski Nov 06 '16 at 22:55
"Why can it be null?" - because of the left join. – Paul Spiegel Nov 06 '16 at 23:04
@PaulSpiegel this is rather a question to the OP for checking whether his data is correctly inserted, not a question to the world why can a value be null in this query. – Kamil Gosciminski Nov 06 '16 at 23:11
Thanks for your help guys, the updated version with the improved join logic worked. – Aidan Nov 07 '16 at 12:18

score 0 · Answer 2 · edited Dec 23 '16 at 01:52

0

SELECT g.first_name,
       g.last_name,
       g.address,
       COALESCE(Sum(b.nights), 0)
FROM   booking b
       RIGHT JOIN guest g
               ON ( b.guest_id = g.id )
WHERE  address LIKE 'edinburgh%'
GROUP  BY g.last_name,
          g.first_name,
          g.address;

edited Dec 23 '16 at 01:52

Blag

5,818
2
22
45

answered Dec 22 '16 at 10:53

Suman Paul

1

4

Please [edit] you question to add an explanation of why do you think using coalesce here is important. Also, please format your code to be readable. – Андрей Беньковский Dec 22 '16 at 10:58

score -1 · Answer 3 · edited May 23 '17 at 11:59

-1

This post answers your questions about how to make the query.

MySQL SUM with same ID

You can simply use COALESCE as referenced here to avoid the NULL Values

How do I get SUM function in MySQL to return '0' if no values are found?

edited May 23 '17 at 11:59

Community

1
1

answered Nov 06 '16 at 21:47

Jeff

143
8

Also since you could have possibility of records with the same names I would highly recommend using the unique record ID in your process – Jeff Nov 06 '16 at 21:49

Join error and order by

3 Answers3