How to join this 3 tables

[zubinbalsara]

CART
----------------
CartID ProductID
1 15
2 17
3 15
3 24
3 89



KITS
-------------
Kitid(PK) ParentProductID ChildProductID
1 15 56
2 15 58
3 15 59
4 17 56


PRODUCTS
-------------------------
ProductID ProductName
15 Insurance Kit
16 Television Kit
17 Paper Clips
24 Towels
56 Life Insurance
58 Car Insurance
59 Home Insurance
89 Telephone Directory



My query is regarding the above 3 tables.
My objective is that given a cartID, I should be able to find the name of the parent product and the name of all the childproducts associated with that parentproduct.
For example if the cartid is 1
Than my answer should be

Insurace Kit (this is the parent Product)
Life Insurance (this is the child Product)
Car Insurance (this is the Child Product)
Home Insurance (this is the child Product)

 

[jbenson001]

There may be a better way, but give this a try:

--Get Parent Products
Select p.ProductName
From Cart c
     Inner Join Kits k ON  c.ProductID = k.ParentProductID
     Inner Join Products p ON k.ParentProductID = p.ProductID
where c.cartid = 1 

UNION

--Get Child Products
Select p.ProductName
From Cart c
     Inner Join Kits k ON  c.ProductID = k.ParentProductID
     Inner Join Products p ON k.childProductID = p.ProductID
where c.cartid = 1

Jim