2 right outer joins ? - 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)

2 right outer joins ?

Author

Topic

dbutler_05
Starting Member

17 Posts

Posted - 2006-01-13 : 14:01:46

Is it possible to use multiple outer joins in one script? I'm expecting this script to pull back all records from table MSPMP100 and all records from summaryBrandGroup. Any suggestions?

SELECT TOP (100) PERCENT SUM(D.QUANA * D.ACTSP) AS ReleasedOrders, B.BrandGroup, R.oregn AS Region, R.descp AS RegionalName
FROM dbo.OBCOP100 AS H INNER JOIN
dbo.OBCOP200 AS D ON H.ORDNO = D.ORDNO AND D.QUANA > 0 AND H.OSTAT = 4 INNER JOIN
dbo.MSPMP100 AS P ON D.PRDNO = P.PRDNO RIGHT OUTER JOIN
dbo.summaryBrandGroup AS B ON P.PTYP2 = B.PTYP2 RIGHT OUTER JOIN
dbo.summaryRegionGroup AS R ON H.OREGN = R.oregn

GROUP BY R.oregn, R.descp, B.BrandGroup

ORDER BY Region

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-13 : 14:13:58

The right joined tables are really the drivers of your query, I think you want


      FROM dbo.summaryRegionGroup	R
 LEFT JOIN dbo.OBCOP100 AS 		H 
	ON H.OREGN = R.oregn
INNER JOIN dbo.summaryBrandGroup AS 	B  
	ON D.PRDNO = P.PRDNO 
 LEFT JOIN dbo.MSPMP100 AS 		P 
	ON P.PTYP2 = B.PTYP2 
INNER JOIN dbo.OBCOP200 AS 		D 
	ON H.ORDNO = D.ORDNO 
       AND D.QUANA > 0 
       AND H.OSTAT = 4

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dbutler_05
Starting Member

17 Posts

Posted - 2006-01-13 : 14:29:49

Sql doesn't like that join. It didn't like the join to d.prdno = p.prdno

Denny

dbutler_05
Starting Member

17 Posts

Posted - 2006-01-13 : 14:33:01

More info: I'm getting "Prdno can't be bound"

Thanks Denny

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-13 : 15:21:27

OK, How's about


SELECT * FROM dbo.OBCOP200 AS 		D 
INNER JOIN (SELECT *  
      FROM dbo.summaryRegionGroup	R
 LEFT JOIN dbo.OBCOP100 AS 		H 
	ON H.OREGN = R.oregn) AS XXX
	ON XXX.ORDNO = D.ORDNO 
       AND D.QUANA > 0 
       AND XXX.OSTAT = 4 
INNER JOIN (SELECT * 
      FROM dbo.summaryBrandGroup AS 	B  
 LEFT JOIN dbo.MSPMP100 AS 		P 
	ON P.PTYP2 = B.PTYP2) AS YYY
	ON D.PRDNO = YYY.PRDNO

Als, read the hint link in my sig and post what it asks for...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources