LEFT JOIN with more than ONE Key (no nulls)

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

LEFT JOIN with more than ONE Key (no nulls)

Author

Topic

schludi
Starting Member

1 Post

Posted - 2010-07-14 : 10:43:25

Hello!

I have a problem doing a LEFT JOIN on two tables which have the following keys:

compcode, salesdiv, market, material

First my SQL (result has 4526 rows (must be 35532 like tmp_all..) with no NULL values want to have them on the right...):


	SELECT		 a.*, b.amount 'actuallastyear' 
	FROM		 tmp_alleivcsetartikel a 
	LEFT JOIN	 SUCCESS.dbo.bulktable_converted b 
	ON	    	 (b.material+'/'+b.compcode+'/'+b.salesdiv+'/'+b.market)=(a.material+'/'+a.compcode+'/'+a.salesdiv+'/'+a.market)   


SAME WITH:

	SELECT		 count(*)
	FROM		 tmp_alleivcsetartikel a 
	LEFT JOIN	 SUCCESS.dbo.bulktable_converted b 
	ON	    	 b.material=a.material AND b.compcode=a.compcode AND b.salesdiv=a.salesdiv AND b.market=a.market

or the same with: ON b.material=a.material AND a.compcode=b.compcode...

The table tmp_alleivcsetartikel has 35532 rows.
SELECT TOP 2 * FROM tmp_alleivcsetartikel:
compcode salesdiv market material
101 AE Domestic 4021800K
101 AE Domestic 4021819

The table SUCCESS.dbo.bulktable_converted has 6288589 rows.
SELECT TOP 2 * FROM SUCCESS.dbo.bulktable_converted:
compcode market salesdiv material month year umsatz amount
101 Domestic AE 100-000-000 1 2009 0 30
101 Domestic AE 100-000-000 2 2009 58 16

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-07-14 : 11:02:10

The FROM clause is evaluated before the WHERE clause, so the filter needs to be applied on the JOIN.


SELECT a.*, b.amount  AS actuallastyear
FROM tmp_alleivcsetartikel a 
	LEFT JOIN SUCCESS.dbo.bulktable_converted b
		ON a.material = b.material
			AND a.compcode = b.compcode
			AND a.salesdiv = b.salesdiv
			AND a.market = b.market
			-- filter needs to be applied here
			AND b.[year] IN (2009)
-- this will filter out nulls from the OUTER JOIN
-- WHERE b.[year] IN (2009)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-07-14 : 11:05:06

You probably need to understand the order in which SQL clauses are evaluated. Roughly:
FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP

This explains why SELECT expressions cannot be referenced in the WHERE clause etc.

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 11:15:25

You definitely should use

ON b.material=a.material AND b.compcode=a.compcode AND b.salesdiv=a.salesdiv AND b.market=a.market

which will be MUCH more efficient than

ON (b.material+'/'+b.compcode+'/'+b.salesdiv+'/'+b.market)=(a.material+'/'+a.compcode+'/'+a.salesdiv+'/'+a.market)

However,


SELECT	 count(*)
FROM	MyTable AS T1
	LEFT JOIN MyOtherTable AS T2
	ON T2.MyCol = T1.MyCol

will never give you a smaller number than SELECT COUNT(*) FROM MyTable. It will give you a larger number if some rows in MyTable match multiple rows in MyOtherTable

If you put a WHERE clause that references MyOtherTable then that will make the LEFT OUTER JOIN into an INNER JOIN - but you didn't say you have a WHERE clause?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources