SQL query

Databases
1

So, I have two tables…

Guests (IdPAX integer, Name text)
Bookings (IdBooking integer, IdPax1 integer, IdPax2 integer, IdPax3 integer, IdPax4 integer)

The bookings table contains the ID’s of the guests that checked in (up to four by booking)

I want to get the total list of guests from the Guest table, but sorted by the (SUM) total amount of times every guest show up in any booking.

Did I make myself clear ? Just want to count how many times every guest checked in and sort the guest table with that logic…

I’ve probablly done this before but I don’t remember how…

Can you help me ?

Thanks

2

Something like

SELECT g.name, COUNT(b.idbooking) FROM guests AS g LEFT OUTER JOIN bookings AS b ON b.idpax1 = g.idpax OR b.idpax2 = g.idpax OR b.idpax3 = g.idpax OR b.idpax4 = g.idpax GROUP BY g.name;

should do it for you.

3

I suggest you rethink your structure. Instead of two tables, use three:

  • Guest
  • Booking
    — id
  • Booking Guest
    — Booking id
    — Guest id

You can use triggers or code to limit it to 4 guests per booking if you want, a Unique check on Booking Guest to make sure the same guest is not listed twice for the same booking, and your calculations and groupings will be much easier.

4

@Kem Tekinay : I agree with you Kem, but it’s too late for that, now.

I’ll try Wayne’s suggestion and see how it goes.

Thank you guys!