Joining 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

General SQL Server Forums

New to SQL Server Programming

Joining tables

Author

Topic

gparadis3
Starting Member

11 Posts

Posted - 2005-12-09 : 00:33:47

Hi all..

Let say i have 3 tables (tblA, tblB, tblC). I want to get all data in tblA. At the same time, i want to join the tables. If value in column aStatus equal to 1, it will get the value from tblB. If the column value is 2, it will refer to tblC. Else, if it 0, no need to refer to any table. just like inner join.. but i dun get the logic of how im going to do that.. can someone advise me on how to do the joining? please refer to picture below.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-09 : 00:43:05

Try this

Select Columns from tblA where aStatus=0
Union All
Select Columns from tblA A inner join tblB B on A.keycol=B.keycol where aStatus=1
Union All
Select Columns from tblA A inner join tblC C on A.keycol=C.keycol where aStatus=2

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-09 : 00:43:32

not so elegant solution

create table #tblA
(
	aID		int identity (1,1),
	aStatus		int,
	aDesc		varchar(50)
)

create table #tblB
(
	bID		int identity (1,1),
	baID		int,
	bExtra		varchar(50)
)


create table #tblC
(
	cID		int identity (1,1),
	caID		int,
	cExtra		varchar(50)
)


insert into #tblA(aStatus, aDesc)
select 0, 'Status = 0'	union all
select 1, 'Status = 1'	union all
select 2, 'Status = 2'

insert into #tblB(baID, bExtra)
select 2, 'tblB'

insert into #tblC(caID, cExtra)
select 3, 'tblC'

select 	aID, aStatus, aDesc, NULL as ID, NULL as Extra
from	#tblA a 
where	a.aStatus	= 0
union all
select 	aID, aStatus, aDesc, bID, bExtra
from	#tblA a left join #tblB b
	on	a.aID	= b.baID
where	a.aStatus	= 1
union all
select 	aID, aStatus, aDesc, cID, cExtra
from	#tblA a left join #tblC c
	on	a.aID	= c.caID
where	a.aStatus	= 2

-----------------
[KH]
Guys, where are we right now ?

gparadis3
Starting Member

11 Posts

Posted - 2005-12-09 : 02:01:28

OK. understood. thanks guys.. both answers are working fine.

If let say there is one table named status. aStatus will refer to that table in order to get status description. so every select statement must add an additional query in order to join that status table.

eg:
Select A.*, s.statusDesc from tblA A inner join tblB B on A.aID=B.baID left join (select * from status) s on A.status=s.status<------- all select statement for union must add this extra query???

and if i want to join 3 or 4 other tables, i have to do the same thing.. TRUE? (must be a very long query)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-09 : 02:13:15

This is Good. Problem is added one by one.

Based on my not so elegant solution


select	aID, aStatus, aDesc, ID, Extra, sDescription
from
(select 	aID, aStatus, aDesc, NULL as ID, NULL as Extra
from	#tblA a 
where	a.aStatus	= 0
union all
select 	aID, aStatus, aDesc, bID, bExtra
from	#tblA a inner join #tblB b
	on	a.aID	= b.baID
where	a.aStatus	= 1
union all
select 	aID, aStatus, aDesc, cID, cExtra
from	#tblA a inner join #tblC c
	on	a.aID	= c.caID
where	a.aStatus	= 2) as r inner join #tblStatus s
	on	r.aStatus	= s.saStatus