join question - 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)

join question

Author

Topic

sunbeam
Starting Member

8 Posts

Posted - 2004-08-16 : 10:49:12

i have 2 tables, one is a look up tbl and the other is tbl with data

tbllookup
s_id status
------------------
0 pass
1 fail

tbldata
-------------
s_id p_id
1 1
0 1
1 1

i am able to join tbldata and tbllookup for s_id
select p.s_id, s.status, p.p_id from tbldata as p
join tbllookup s on (p.s_id = s.s_id)

how do i get the same for p_id too?

thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-16 : 10:56:26

This work for ya?


select 
	t.s_id, 
	s.status, 
	t.p_id,
	p.status
from tbldata as t
Inner join tbllookup s 
on t.s_id = s.s_id
Inner join tbllookup p
on t.p_id = p.s_id

Corey

sunbeam
Starting Member

8 Posts

Posted - 2004-08-16 : 11:19:57

thank u for that input,
the look up seemed to work,
except reduced my table from 80 records to 2 ???

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-16 : 11:32:13

Change the 'inner' to 'left':

select
t.s_id,
s.status,
t.p_id,
p.status
from tbldata as t
left join tbllookup s
on t.s_id = s.s_id
left join tbllookup p
on t.p_id = p.s_id

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-16 : 11:36:24

you've given use too little to work with. give us more data on table structure and data.

Go with the flow & have fun! Else fight the flow :)

sunbeam
Starting Member

8 Posts

Posted - 2004-08-16 : 12:21:26

here u go,
for the db structure

tblStatus is linked to tblC which is linked to two tables(inner)
1)tblP1(needs to call 2 look up tables tblLookUp1, tblLookup2)
2)tblP2

my above post was in regards to tblstaus

my entire join statement looks like this:

select whatever
from tblStatus s
right join tblC c(inner join tblP1(left join tblLookup1, left join tblLookup2)
inner join tblP2)
where (s.id = c.id)

any suggestions if this is nested correctly
and
where should the second insert inner join() tblstatus s1 go?(from ur post)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-16 : 14:14:18

and this works for you???

where are the linking fields??? you need to do

select whatever
from tblStatus s
right join tblC c on (s.id = c.id)
inner join tblP1 P1 on (someFields)
inner join tblP2 P2 on (someFields)
left join tblLookup1 L1 on (someFields)
left join tblLookup2 L2 on (someFields)

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources