3 TBL join - Help Please - 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

Transact-SQL (2000)

3 TBL join - Help Please

Author

Topic

dfribley
Starting Member

2 Posts

Posted - 2006-02-11 : 17:34:31

"Hi Everybody"

With the following tables:

Table TBLStages (id,desc)
1, 'Stage 1'
2, 'Stage 2'
3, 'Stage 3'

Table TBLStatus (id, FK_stage_id, desc)
1, 1, 'status 1-1'
2, 1, 'status 1-2'
3, 2, 'status 2-1'
4, 2, 'status 2-2'
5, 3, 'status 3-1'
6, 3, 'status 3-2'

Table TBLPropStatus (FK_prop_id, FK_status_id, dateTime)
1, 1, 1/1/00
1, 3, 1/1/00
2, 1, 2/5/01
3, 1, 3/1/02

I'm having a hard time putting together a query that does what I want. Basically, for every FK_prop_id, I want to be able to return a query like this:

FOR FK_prop_id = 1:
--------------------

Stage 1: status 1-1
Stage 2: status 2-1
Stage 3: NULL

I know the starting point would be to query all of TBLStages and LEFT OUTER JOIN the the other tables, but I'm not experienced enough to know how to join the other two tables before joing the Stage table.

Sorry if this doesn't make sense; thank you for your help!
-Dave Fribley

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-11 : 18:05:51

I have no idea if this query will hold up for different scenarios but for this sample data and for prop_id = 1, it seems to work:


use pubs
go
set nocount on

create Table TBLStages (id int primary key, [desc] varchar(20))
insert tblStages
select 1, 'Stage 1' union all
select 2, 'Stage 2' union all
select 3, 'Stage 3'
go

create Table TBLStatus (id int primary key, FK_stage_id int references tblstages(id), [desc] varchar(20))
insert tblstatus
select 1, 1, 'status 1-1' union all
select 2, 1, 'status 1-2' union all
select 3, 2, 'status 2-1' union all
select 4, 2, 'status 2-2' union all
select 5, 3, 'status 3-1' union all
select 6, 3, 'status 3-2'

go
create Table TBLPropStatus (FK_prop_id int, FK_status_id int references tblstatus(id), dt dateTime)
insert tblPropStatus
select 1, 1, '1/1/00'  union all
select 1, 3, '1/1/00' union all
select 2, 1, '2/5/01' union all
select 3, 1, '3/1/02'
go

select	a.[desc] stageDesc
	,b.[desc] statusDesc
from	tblStages a
left join	(
		select	a.fk_stage_id, a.[desc]
		from	tblstatus a
		join	tblPropStatus b
				on b.fk_status_id = a.id
		where	fk_prop_id = 1
		) b
			on b.fk_stage_id = a.id



drop table tblpropstatus
go
drop table tblstatus
go
drop table tblstages

EDIT:
Output


stageDesc            statusDesc           
-------------------- -------------------- 
Stage 1              status 1-1
Stage 2              status 2-1
Stage 3              NULL

Be One with the Optimizer
TG

dfribley
Starting Member

2 Posts

Posted - 2006-02-11 : 18:23:59

Thanks TG

That answered my question. I was having a huge brain fart, and it was nice to take a quick glance at your code.

-Dave Fribley

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources