SQL Select,join

Question

I need some help on writing the correct select statement...

I have 3 tables (1 reservation can have a primary KidsClubGuest and one KidsClubGuest can have multiple KidsClubChild).

1. KidsClubReservation
2. KidsClubGuest
3. KidsClubChild

I am trying to find a suitable select statement which displays all the KidsClubChild rows where ReservationID = 354 for example.

I have tried this which brings 58 records, thats all the children in the table.

SELECT KCC.*
FROM KidsClubChild KCC
INNER JOIN KidsClubGuest KCG ON KCC.GuestID = KCG.GuestID

Then the moment I try the below, the return result is 1558 and based on the below if I try to use a where clause then its brings the wrong result set...

SELECT KCC.*
FROM KidsClubChild KCC
INNER JOIN KidsClubGuest KCG ON KCC.GuestID = KCG.GuestID
INNER JOIN KidsClubReservation KCR ON KCG.GuestID = KCR.GuestID

SELECT KCC.*
FROM KidsClubChild KCC
INNER JOIN KidsClubGuest KCG ON KCC.GuestID = KCG.GuestID
INNER JOIN KidsClubReservation KCR ON KCG.GuestID = KCR.GuestID
WHERE KCR.ReservationID = 354

Answer 1

Hi，@Ioannis Ioannou - Software Engineer

Welcome to Microsoft T-SQL Q&A Forum!

How about this：

SELECT distinct(KCC.ChildID),GuestID,Firstname,LastName,DOB,ReservationID FROM    
(select * from KidsClubChild KCC  
INNER JOIN KidsClubGuest KCG ON KCC.GuestID = KCG.GuestID  
INNER JOIN KidsClubReservation KCR ON KCG.GuestID = KCR.GuestID  
)temp  

WHERE KCR.ReservationID = 354  

As naomi said, you might as well try to modify the design of diagrams. You are using the correct multi-table connection, but there is only a problem in the data connection process.
You did not provide us with some test data, we can only Try to modify the join condition according to your description. If this doesn't work, please provide some data for us to test this against.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Normally you start FROM clause from the parent and then drill down to children (although order of INNER joins should not matter).

Let's try to verify step by step:

select ReservationId, GuestId from from KidsClubReservation reservation where reservation.ReservationID = 354 --
do you get all guests associated with this reservation correctly?

If yes, let's proceed:
;with cteReservations as (select ReservationId, GuestId from from KidsClubReservation reservation where reservation.ReservationID = 354)

select * from KidsClubChild ch inner join cteReservations res on ch.GuestID = res.GuestID -- should give you all the children for particular reservation

Answer 3

Full query would be:

;with cteReservations as (select ReservationId, GuestId from from KidsClubReservation reservation where reservation.ReservationID = 354)

select * from KidsClubChild ch inner join cteReservations res on ch.GuestID = res.GuestID -- should give you all the children for particular reservation

Answer 4

Do you need kids in a particular session? For your reservation 354, there is guestId 170 and all kids seem to have 170 as their guest, so your result is not surprising. How exactly do you get 3 kids only? What would you get if you try

 ;with cteReservations as (select ReservationId, GuestId from from KidsClubReservation reservation where reservation.ReservationID = 354)

 select ch.* from KidsClubChild ch 
where exists (select 1 from
cteReservations res where ch.GuestID = res.GuestID) -- should give you all the children for particular reservation under same guest ID

Answer 5

Yes I think my design is wrong then...

I tried what you suggested but I am still getting all the kids where GuestID is 170 and this results in 50 records. Because the idea of this is to retrieve only the kids associated with reservation ID 354.

So if I have another reservation e.g 355 and GuestID is 171 and that guest has 2 children associated with the reservation 355 then I should be able to retrieve only those 2 children. Now if the same guest with GuestID 171 makes another reservation e.g 356 and ads only one child then I should be able to retrieve that child only by searching with ReservationID 356.

Appreciate the help.