LEFT 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

General SQL Server Forums

New to SQL Server Programming

LEFT OUTER JOIN

Author

Topic

ruchijani
Starting Member

23 Posts

Posted - 2009-10-26 : 00:54:18

Hello

I have tables like below

table1



EID          ENAME
1             XYZ
2             ABC
3             DEF

table2 
EID       EMON     ENo   EYEAR
1         1         8     1
2         1         2     1
1         2         4     1
2         2         5     1
3         1         7     2

Now i want to select all EName from table1 even if there is no EID in table2 i tried with left outer join but below query did not work for me

SELECT ENAME,table1.EID,ISNULL(SUM(CASE WHEN EMON = 1 THEN ENo END),0) as col1,ISNULL(SUM(CASE WHEN EMON = 2 THEN ENo END),0)
as col2 FROM table1 left outer JOIN table2 ON table1.EID = table2.EID WHERE EYEAR = 1 GROUP BY table1.EID,ENAME

Thanks
Ruchi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-26 : 01:07:00

put the condition on the LEFT JOIN table on the ON clause. Putting it on the WHERE clause change the effect of the join to INNER JOIN


SELECT	
	ENAME,
	table1.EID,
	ISNULL(SUM(CASE WHEN EMON = 1 THEN ENo END),0) AS col1,
	ISNULL(SUM(CASE WHEN EMON = 2 THEN ENo END),0) AS col2 
FROM 	
	table1 
	LEFT OUTER JOIN table2 	ON 	table1.EID 	= table2.EID 
				AND	table2.EYEAR	= 1
WHERE 		
	EYEAR = 1 
GROUP BY 
	table1.EID,
	ENAME

KH
[spoiler]Time is always against us[/spoiler]

ruchijani
Starting Member

23 Posts

Posted - 2009-10-26 : 01:21:00

quote:
Originally posted by khtan

put the condition on the LEFT JOIN table on the ON clause. Putting it on the WHERE clause change the effect of the join to INNER JOIN

SELECT
ENAME,
table1.EID,
ISNULL(SUM(CASE WHEN EMON = 1 THEN ENo END),0) AS col1,
ISNULL(SUM(CASE WHEN EMON = 2 THEN ENo END),0) AS col2
FROM
table1
LEFT OUTER JOIN table2 ON table1.EID = table2.EID
AND table2.EYEAR = 1
WHERE
EYEAR = 1
GROUP BY
table1.EID,
ENAME

KH
[spoiler]Time is always against us[/spoiler]

Thank u khtan

Thanks
Ruchi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources