Joins with case statement - 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

Joins with case statement

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-11-16 : 03:39:07

DECLARE @SAPGL TABLE(GLNO CHAR(10), ENTITY_ID CHAR(4), PRIMARY KEY (GLNO, ENTITY_ID))
INSERT INTO @SAPGL SELECT
'1234567890','1000' UNION ALL SELECT
'1234567890','2000' UNION ALL SELECT
'1234567891','1000' UNION ALL SELECT
'1234567892','2000';
DECLARE @MYGL TABLE(GLNO CHAR(10), ENTITY_FG CHAR(1), PRIMARY KEY (GLNO, ENTITY_FG))
INSERT INTO @MYGL SELECT
'1234567890','Y' UNION ALL SELECT
'1234567890','N' UNION ALL SELECT
'1234567891','Y' UNION ALL SELECT
'1234567892','N';

SELECT *
FROM @SAPGL SAPGL LEFT JOIN @MYGL MYGL
ON SAPGL.GLNO = MYGL.GLNO
AND SAPGL.ENTITY_ID = CASE WHEN ENTITY_FG = 'Y' THEN '1000' ELSE '2000' END

This query perform correctly but it look ugly, any trick to change it into normal join without case when?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 04:34:14

SELECT *
FROM @SAPGL SAPGL LEFT JOIN @MYGL MYGL
ON SAPGL.GLNO = MYGL.GLNO
AND (SAPGL.ENTITY_ID = '1000' and ENTITY_FG = 'Y' or SAPGL.ENTITY_ID = '2000' and ENTITY_FG <> 'Y')

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-16 : 22:20:17

quote:
Originally posted by madhivanan

SELECT *
FROM @SAPGL SAPGL LEFT JOIN @MYGL MYGL
ON SAPGL.GLNO = MYGL.GLNO
AND ((SAPGL.ENTITY_ID = '1000' and ENTITY_FG = 'Y') or (SAPGL.ENTITY_ID = '2000' and ENTITY_FG <> 'Y'))

Madhivanan

Failing to plan is Planning to fail

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-16 : 22:23:20

or use CTE or Derived Table to make it looks nicer


SELECT	*
FROM	@SAPGL SAPGL
LEFT JOIN
	(
		SELECT	GLNO, ENTITY_ID = CASE WHEN ENTITY_FG = 'Y' THEN '1000' ELSE '2000' END, ENTITY_FG
		FROM	@MYGL
	) MYGL	ON	SAPGL.GLNO 	= MYGL.GLNO
		AND	SAPGL.ENTITY_ID	= MYGL.ENTITY_ID

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-17 : 04:39:30

quote:
Originally posted by khtan

quote:
Originally posted by madhivanan

SELECT *
FROM @SAPGL SAPGL LEFT JOIN @MYGL MYGL
ON SAPGL.GLNO = MYGL.GLNO
AND ((SAPGL.ENTITY_ID = '1000' and ENTITY_FG = 'Y') or (SAPGL.ENTITY_ID = '2000' and ENTITY_FG <> 'Y'))

Madhivanan

Failing to plan is Planning to fail

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

Thanks

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-17 : 11:57:48

Also you need to be carful with LEFT JOINS. There is a known "feature" that if you try to apply predicates to the ON clause of the join to the LEFT table they will not be applied and you think it might be.:

DECLARE @SAPGL TABLE(GLNO CHAR(10), ENTITY_ID CHAR(4), PRIMARY KEY (GLNO, ENTITY_ID))
INSERT INTO @SAPGL SELECT
'1234567890','1000' UNION ALL SELECT
'1234567890','2000' UNION ALL SELECT
'1234567891','1000' UNION ALL SELECT
'1234567892','2000' UNION ALL SELECT
-- Added some dummy values
'1234567892','3000' UNION ALL SELECT
'1234567892','4000';
DECLARE @MYGL TABLE(GLNO CHAR(10), ENTITY_FG CHAR(1), PRIMARY KEY (GLNO, ENTITY_FG))
INSERT INTO @MYGL SELECT
'1234567890','Y' UNION ALL SELECT
'1234567890','N' UNION ALL SELECT
'1234567891','Y' UNION ALL SELECT
'1234567892','N';

-- Works
SELECT *
FROM @SAPGL AS SAPGL 
INNER JOIN @MYGL AS MYGL
ON SAPGL.GLNO = MYGL.GLNO
AND SAPGL.ENTITY_ID = CASE WHEN ENTITY_FG = 'Y' THEN '1000' ELSE '2000' END

-- Doesn't work
SELECT *
FROM @SAPGL SAPGL LEFT JOIN @MYGL MYGL
ON SAPGL.GLNO = MYGL.GLNO
    AND 
    (
        (SAPGL.ENTITY_ID = '1000' and ENTITY_FG = 'Y') 
        OR (SAPGL.ENTITY_ID = '2000' and ENTITY_FG <> 'Y')
    )

--Works
SELECT *
FROM @SAPGL SAPGL INNER JOIN @MYGL MYGL
ON SAPGL.GLNO = MYGL.GLNO
    AND 
    (
        (SAPGL.ENTITY_ID = '1000' and ENTITY_FG = 'Y') 
        OR (SAPGL.ENTITY_ID = '2000' and ENTITY_FG <> 'Y')
    )

--Works    
SELECT *
FROM @SAPGL SAPGL LEFT JOIN @MYGL MYGL
ON SAPGL.GLNO = MYGL.GLNO
    AND 
    (
        (SAPGL.ENTITY_ID = '1000' and ENTITY_FG = 'Y') 
        OR (SAPGL.ENTITY_ID = '2000' and ENTITY_FG <> 'Y')
    )
WHERE 
    MYGL.GLNO IS NOT NULL

--Works    
SELECT *
FROM @SAPGL SAPGL LEFT JOIN @MYGL MYGL
ON SAPGL.GLNO = MYGL.GLNO
WHERE
    (SAPGL.ENTITY_ID = '1000' and ENTITY_FG = 'Y') 
    OR (SAPGL.ENTITY_ID = '2000' and ENTITY_FG <> 'Y')

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-11-17 : 21:25:29

dear all, thx for the reply, and sorry for late reply

about lamprey concern on left join, is ok for my situation

teach, i cannot use cte coz i cannot change the query to another looks, derived or cte, eventho both of the table have more den 30m records =,=

i had to admit, ikea do suck bloods x.x

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources