Anon5678
Anon5678

Reputation: 27

SQL Query to identify accommodation booked

I need to know what SQL statement to use in an accommodation database looking for a trip user

Upvotes: 0

Views: 410

Answers (1)

GMB
GMB

Reputation: 222482

The error message is clear enough: the group by clause needs to be consistent with the select. Some databases are smart enough to understand that the name of the customer is functionally dependent on its id, and do not require that you put the name in the group by - but not SQL Server.

Also, you need to count on something that comes from the left joined table if you want 0 for customers without bookings.

Consider:

select c.customer_id, c.customer_name, count(ab.customer_id) as [number of accomm slots]
from customers c
left join  accommodation_bookings ab on c.customer_id = ab.customer_id
group by c.customer_id, c.customer_name

I would take one step forward and pre-aggregate in a subquery. This is usually more efficient:

select c.customer_id, c.customer_name, coalesce(ab.cnt, 0) [number of accomm slots]
from customers c
left join (
    select customer_id, count(*) cnt
    from accommodation_bookings 
    group by customer_id
) ab on c.customer_id = ab.customer_id

You could also express this with a correlated subquery, or a lateral join:

select c.customer_id, c.customer_name, ab.*
from customers c
outer apply (
    select count(*) [number of accomm slots]
    from accommodation_bookings ab
    where c.customer_id = ab.customer_id
) ab

This would take advantage of an index on accommodation_bookings(customer_id) (which should already be there if you have set up a foreign key).

Note: don't use single quotes for identifiers - they are meant for literal strings. In SQL Server, use the square brackets instead.

Upvotes: 1

Related Questions