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

Your tables design is dubious. You can get chidlren for reservation for particular session using your KidsClubChildSession table (or for all sessions for particular reservation). So, we may try going from the KidsClubGuest table into KidsClubReservation then into KidsClubChildSession to get children for reservation (may be many sessions). Not sure it would help, but may be, e.g.

;with cteReservations as (select ReservationId, GuestId from KidsClubReservation reservation where reservation.ReservationID = 354),
cteSessions as (select res.GuestNo, sess.Child from KidsClubChildSession sess inner join cteReservations res on sess.ReservationID = res.ReservationID)

select ch.* from KidsClubChild ch inner join cteSessions sess on ch.ChildID = sess.ChildID where exists (select 1 from KidsClubGuest g where g.GuestNO = sess.GuestNo) -- may want to look up particular guest

Last attempt to get kids we need.

Answer 3

I believe this is the best statement I have

SELECT DISTINCT(KCCS.ChildID), KCC.*
FROM KidsClubChild KCC
INNER JOIN KidsClubChildSession KCCS ON KCC.ChildID = KCCS.ChildID
WHERE KCCS.ReservationID = 354

it produced 2 records which is correct...

Thank you all for your help