Left Outer Join with multiple conditions

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 2005 Forums

Transact-SQL (2005)

Left Outer Join with multiple conditions

Author

Topic

itsmaheshp
Starting Member

3 Posts

Posted - 2010-10-14 : 13:21:27

Hi All,
I have the below tables:
TABLE A:
volumeid, version, column1, column2, column3

TABLE B:
workid, taskid, status

TABLE C:
workid, attributeid, attrval

* there can be multiple volumeid with different versions in TABLE A but I am interested only in version = -1
* there are multiple records for workid in Table B with different taskids and differnt status for each taskid.
* there are multiple records for workid in Table C with different attributeid & attrval.
* I want to left outer join Table A & B so that I will get all records from A and if matching records from B (where status =2,3,4)
and then inner join with C.

here is what I have.
select a.* from A left outer join B on a.volumeid = b.workid and a.version = -1 and b.status in (2,3,5)
inner join C on a.volumeid = c.workid

But this one is giving me 2 recs one with blank columns except volumeid.

Any clue?

Thanks,
Mahesh

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 13:41:42

Please give us sample data and expected output.

No, you're never too old to Yak'n'Roll if you're too young to die.

itsmaheshp
Starting Member

3 Posts

Posted - 2010-10-14 : 14:43:52

Table A:
1000, -1, col1,col2,col3
1000, 1, col11, col21,col31
1000, 2, col12, col22,col32
1000, 3, col13, col23,col33
1001, -1,val1,val2,val3
1001, 1,val11,val21,val31
1001, 2,val12,val22,val32
1001, 3,val13,val23,val33

Table B:
1000,1,2
1000,2,-1
1000,3,-2
1001,1,-1
1001,2,-2
1001,3,-1

Table C:
1000,1,val1
1001,2,val3

Result: ( where version= -1 & b.status in (2,3)
a.volumeid,a.column1,column2,column3,b.status,c.attrval
1000,col1,col2,col3,2,val1
1001,val1,val2,val3,,val3

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-14 : 15:22:22

[code]--Set up sample data

DECLARE @TableA TABLE (volumeid int, version int, column1 varchar(50), column2 varchar(50), column3 varchar(50))
INSERT @TableA VALUES
(1000, -1, 'col1','col2','col3'),
(1000, 1, 'col11', 'col21','col31'),
(1000, 2, 'col12', 'col22','col32'),
(1000, 3, 'col13', 'col23','col33'),
(1001, -1,'val1','val2','val3'),
(1001, 1,'val11','val21','val31'),
(1001, 2,'val12','val22','val32'),
(1001, 3,'val13','val23','val33')

DECLARE @TableB TABLE(workid int, taskid int, status int)
INSERT @TableB VALUES
(1000,1,2),
(1000,2,-1),
(1000,3,-2),
(1001,1,-1),
(1001,2,-2),
(1001,3,-1)

DECLARE @TableC TABLE (workid int, attributeid int, attrval varchar(50))
INSERT @TableC VALUES
(1000,1,'val1'),
(1001,2,'val3')

-- Select results
select
a.*
from
@TableA AS A
left outer join
@TableB AS B
on a.volumeid = b.workid
and b.status in (2,3,5)
inner join
@TableC AS C
on a.volumeid = c.workid
where
a.version = -1
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-14 : 15:23:31

I forgot to add you cannot a restriction to the left table on a left outer join, rather you must add it to the where clause. It's been a known "feature" of SQL for many versions.

itsmaheshp
Starting Member

3 Posts

Posted - 2010-10-14 : 15:56:37

oh got it.
Thank you for your quick response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources