UNION ALL - LEFT JOIN - WEIRD - 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

General SQL Server Forums

New to SQL Server Programming

UNION ALL - LEFT JOIN - WEIRD

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2008-04-15 : 11:29:39

Simple request and I am going nuts!

Simple Explanation:
I have a table with numbers 1-5, I want to left join a table with numbers 1-10, and show a sum... So since numbers 6-10 will not match anything, I will have a sum of zero for those rows. Simple, I thought!


SELECT SUM(ISNULL(RI.quantity,0)) AS Quantity, usr.conversionRate, usr.CorpType
FROM ( SELECT 0 AS conversionRate, 1 AS CorpType UNION ALL SELECT .05, 1 UNION ALL SELECT .5, 1 UNION ALL SELECT 1, 1 UNION ALL  
		SELECT 1.5, 1 UNION ALL SELECT 1.75, 1 ID UNION ALL SELECT 2, 1 UNION ALL SELECT 0, 2 UNION ALL
		SELECT .05, 2 UNION ALL SELECT .5, 2 UNION ALL SELECT 1, 2 UNION ALL SELECT 1.5, 2 UNION ALL
		SELECT 1.75, 2 ID UNION ALL SELECT 2, 2 ) usr
	LEFT OUTER JOIN Items I
		ON I.ConversionRate = usr.conversionRate
	LEFT OUTER JOIN Requests_Items RI
		ON I.ID = RI.itemID 
			AND ISNULL(RI.impactMonth,RI.dueDate) >= '02/01/2008' and ISNULL(RI.impactMonth,RI.dueDate) < '03/01/2008'
	LEFT OUTER JOIN Corporations_Requests CR
		ON CR.requestID = RI.requestID
	LEFT OUTER JOIN Corporations C
		ON C.CorpID = CR.corporationID
			AND C.CorpType = usr.CorpType
GROUP BY usr.CorpType, usr.conversionRate
ORDER BY usr.CorpType, usr.conversionRate

I have no clue why this doesn't work. It appears that the UNION ALL(s) are being crossed joined. Any light shed on this would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 12:00:26

Change like this & try

SELECT SUM(CASE WHEN RI.quantity IS NOT NULL THEN RI.quantity ELSE 0 END ) AS Quantity, usr.conversionRate, usr.CorpType
FROM ( SELECT 0 AS conversionRate, 1 AS CorpType UNION ALL SELECT .05, 1 UNION ALL SELECT .5, 1 UNION ALL SELECT 1, 1 UNION ALL  
		SELECT 1.5, 1 UNION ALL SELECT 1.75, 1 ID UNION ALL SELECT 2, 1 UNION ALL SELECT 0, 2 UNION ALL
		SELECT .05, 2 UNION ALL SELECT .5, 2 UNION ALL SELECT 1, 2 UNION ALL SELECT 1.5, 2 UNION ALL
		SELECT 1.75, 2 ID UNION ALL SELECT 2, 2 ) usr
	LEFT OUTER JOIN Items I
		ON I.ConversionRate = usr.conversionRate
	LEFT OUTER JOIN Requests_Items RI
		ON I.ID = RI.itemID 
			AND ISNULL(RI.impactMonth,RI.dueDate) >= '02/01/2008' and ISNULL(RI.impactMonth,RI.dueDate) < '03/01/2008'
	LEFT OUTER JOIN Corporations_Requests CR
		ON CR.requestID = RI.requestID
	LEFT OUTER JOIN Corporations C
		ON C.CorpID = CR.corporationID
			AND C.CorpType = usr.CorpType
GROUP BY usr.CorpType, usr.conversionRate
ORDER BY usr.CorpType, usr.conversionRate

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2008-04-15 : 12:03:41

Still appears to be cross-joining.

Quantity	conversionRate	CorpType
24072	0.00	1
0	0.05	1
784155	0.50	1
45700	1.00	1
284347	1.50	1
0	1.75	1
0	2.00	1
24072	0.00	2
0	0.05	2
784155	0.50	2
45700	1.00	2
284347	1.50	2
0	1.75	2
0	2.00	2

Should be returning...

Quantity	conversionRate	CorpType
24072	0.00	1
0	0.05	1
784155	0.50	1
45700	1.00	1
284347	1.50	1
0	1.75	1
0	2.00	1
0	0.00	2
0	0.05	2
0	0.50	2
0	1.00	2
0	1.50	2
0	1.75	2
0	2.00	2

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2008-04-15 : 12:07:59

This seems to work but not sure if it is correct.

SELECT
	--SUM(CASE WHEN RI.quantity IS NOT NULL THEN RI.quantity ELSE 0 END ) AS Quantity, 
	SUM(CASE WHEN C.CorpType IS NOT NULL THEN RI.quantity ELSE 0 END ) AS Quantity,
	usr.conversionRate, usr.CorpType
FROM ( SELECT 0 AS conversionRate, 1 AS CorpType UNION SELECT .05, 1 UNION SELECT .5, 1 UNION SELECT 1, 1 UNION 
		SELECT 1.5, 1 UNION SELECT 1.75, 1 ID UNION SELECT 2, 1 UNION SELECT 0, 2 UNION 
		SELECT .05, 2 UNION SELECT .5, 2 UNION SELECT 1, 2 UNION SELECT 1.5, 2 UNION 
		SELECT 1.75, 2 ID UNION SELECT 2, 2 ) usr
	LEFT OUTER JOIN Items I
		ON I.ConversionRate = usr.conversionRate
	LEFT OUTER JOIN Requests_Items RI
		ON I.ID = RI.itemID 
			AND ISNULL(RI.impactMonth,RI.dueDate) >= '02/01/2008' and ISNULL(RI.impactMonth,RI.dueDate) < '03/01/2008'
	LEFT OUTER JOIN Corporations_Requests CR
		ON CR.requestID = RI.requestID
	LEFT OUTER JOIN Corporations C
		ON C.CorpID = CR.corporationID
			AND C.CorpType = usr.CorpType
GROUP BY usr.CorpType, usr.conversionRate
ORDER BY usr.CorpType, usr.conversionRate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 12:16:47

if you are getting the values you want that should be correct query. We cant tell an accurate answer unless we know about your tables,the data they contain and full requirement you are trying to arrive at.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources