Nested outer join - 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)

Nested outer join

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 09:21:43

How do I do a nested outer join?

I want to join tables A B and C

I want all rows from table A

I want any matching rows from table B, if they exist, but ONLY if rows also existing in table C

FWIW the PK for Table C has columns from both tables A and B

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-16 : 09:24:58

try
from A left join (B inner join C on b.id = c.id) on a.Id = b.Id

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

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 10:02:18

Darn it, I had my brackets in the wrong place, I'll give that a whirl, thanks

Kristen

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 10:41:00

:-) One of my columns is not in scope, I'm stuck


CREATE TABLE tblA
(
	A_ID	varchar(10),
	A_XXX	varchar(10)
)

CREATE TABLE tblB
(
	B_ID	varchar(10),
	B_XXX	varchar(10),
	B_C_ID	varchar(10)
)

CREATE TABLE tblC
(
	C_ID	varchar(10),
	C_B_ID	varchar(10),
	C_D_ID	varchar(10)
)

CREATE TABLE tblD
(
	D_ID	varchar(10),
	D_A_ID	varchar(10),
)

INSERT INTO tblA
SELECT 'A1', 'XXX1'  UNION ALL
SELECT 'A2', 'XXX2'	-- Should show with no joined data

INSERT INTO tblB
SELECT 'B101', 'XXX1', 'C201' UNION ALL
SELECT 'B102', 'XXX1', 'C201' UNION ALL
SELECT 'B103', 'XXX2', 'C202' 	-- Should not show

INSERT INTO tblC
SELECT 'C201', 'B101', 'D301' UNION ALL
SELECT 'C202', 'B102', 'D302'

INSERT INTO tblD
SELECT 'D301', 'A1' UNION ALL
SELECT 'D302', 'A1'


SELECT *
FROM dbo.TblA
	JOIN dbo.TblB	-- All catalogues for this Category's Brand
		 ON B_XXX = A_XXX
	LEFT OUTER JOIN 
	(	dbo.TblC	-- All products for these Catalogues
		JOIN dbo.TblD
			 ON D_ID = C_D_ID
			AND D_A_ID = A_ID
	)
		 ON C_B_ID = B_ID


DROP TABLE TblA
DROP TABLE TblB
DROP TABLE TblC
DROP TABLE TblD

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-16 : 10:50:02

could you post the expected results too, please?

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-16 : 10:55:10

maybe this?


SELECT *
FROM 	dbo.TblA
	JOIN dbo.TblB ON B_XXX = A_XXX -- All catalogues for this Category's Brand
	LEFT JOIN 
	(	
		dbo.TblC	-- All products for these Catalogues
		JOIN dbo.TblD ON D_ID = C_D_ID
	)
	ON C_B_ID = B_ID AND D_A_ID = A_ID

if not, i could really use some ~~sample data~~ desired results

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

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 11:17:18

"could you post the expected results too, please?"

Iw as afraid you'd ask that - I should have used some realy tables and data!

But I couldn't get past a syntax error. Your syntax looks right, I'll give it a blast.

Thanks

Kristen

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 11:32:17

Works a treat on my live data, thanks very much

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-16 : 11:34:55

you help me, i help you. that's what it's all about.
Live long and prosper my friend.

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