How to join multiple tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to join multiple tables

Author

Topic

zubinbalsara
Starting Member

2 Posts

Posted - 2006-07-10 : 22:27:02

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)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-10 : 22:30:58

did you try to JOIN the tables?

select...
from table1
inner join table2 on table1.pk1=table2.fk1
...
where...

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-11 : 01:35:28

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-11 : 07:52:24

Here you go, zubinbalsara. Make sure you have a read of the links madhivanan provided, and make sure you understand the solution below...

--data
declare @CART table (CartID int, ProductID int)
insert @CART
          select 1, 15
union all select 2, 17
union all select 3, 15
union all select 3, 24
union all select 3, 89

declare @KITS table (Kitid int primary key, ParentProductID int, ChildProductID int)
insert @KITS
          select 1, 15, 56
union all select 2, 15, 58
union all select 3, 15, 59
union all select 4, 17, 56

declare @PRODUCTS table (ProductID int primary key, ProductName varchar(20))
insert @PRODUCTS
          select 15, 'Insurance Kit'
union all select 16, 'Television Kit'
union all select 17, 'Paper Clips'
union all select 24, 'Towels'
union all select 56, 'Life Insurance'
union all select 58, 'Car Insurance'
union all select 59, 'Home Insurance'
union all select 89, 'Telephone Directory'

--inputs
declare @CartID int
set @CartID = 1

--calculation
select ProductName + ' (this is the parent Product)'
from @CART c
    inner join @PRODUCTS p on c.ProductID = p.ProductID
where CartID = @CartID

union all

select ProductName + ' (this is the child Product)'
from @CART c
    inner join @KITS k on c.ProductID = k.ParentProductID
    inner join @PRODUCTS p on k.ChildProductID = p.ProductID
where CartID = @CartID

/*results
------------------------------------------------- 
Insurance 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)
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources