Filip Grznár Filip Grznár - 3 months ago 7
SQL Question

Select records with asociated records of specific type

We have 2 tables Booking, Ticket. Ticket.BookingId is foreign key to Booking.Id:
enter image description here

How to write SQL select (compatible with MS SQL Server) for select bookings which has only tickets of type 1 or 3 or 7?

Something as:

select Booking.Id
from Booking
join Ticket on Ticket.BookingId = Booking.Id
group by Booking.Id
having Ticket.Type IN (1,3,7)


For example, in this case:

enter image description here

final result will: 2,3.

Booking.Id = 1 is not in final result because in table Ticket is ticket (Id = 4, BookingId = 1, Type = 8), so this booking contains also other ticket type than 1 or 3 or 7

Solution in LINQ:

var result = (from b in Booking
join t in Ticket on Booing.Id equals Ticket.BooingId
group new {b,t} by b.Id).Where(group => group.Where(itemOfGroup=>itemOfGroup.Ticket.Type != 1 && itemOfGroup.Ticket.Type != 3 && itemOfGroup.Ticket.Type != 7).Count() == 0 ).Select(group => group.Key);

Answer

Group by the booking and take only those groups having no other types

select Booking.Id
from Booking 
join Ticket on Ticket.BookingId = Booking.Id
group by Booking.Id 
having sum(case when Ticket.Type NOT IN (1,3,7) then 1 else 0 end) = 0
Comments