using case in 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

SQL Server 2008 Forums

Transact-SQL (2008)

using case in join

Author

Topic

svibuk
Yak Posting Veteran

62 Posts

Posted - 2013-08-14 : 01:46:36

i have a subquery in which i need to use case statement for using join

have the following

MID IN (SELECT HId
FROM TBLDETAILS D
INNER JOIN TBLMAS M on
M.ID= D.DID
WHERE M.MID<>0 )

need to add a case statement as below
but it gives error
MID IN (SELECT HId
FROM TBLDETAILS D
INNER JOIN TBLMAS M on
case when type='M' THEN
M.ID= D.DID
ELSE
M.MID=D.MID
END
WHERE M.MID<>0)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-14 : 03:01:39

try this...

MID IN (SELECT HId FROM TBLDETAILS D 
		INNER JOIN TBLMAS M on
		(M.ID = case when type='M' THEN D.DID END)
		OR (M.MID = CASE WHEN type !='M' THEN D.MID END)
		WHERE M.MID<>0)

--
Chandu

svibuk
Yak Posting Veteran

62 Posts

Posted - 2013-08-14 : 03:31:31

Thanks very much
solved my issue

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-14 : 03:45:31

quote:
Originally posted by svibuk

Thanks very much
solved my issue

welcome

--
Chandu

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 07:41:24

MID IN (SELECT HId
FROM TBLDETAILS D
INNER JOIN TBLMAS M on
( type='M' and M.ID= D.DID)
INNER JOIN TBLMAS M2 on
M2.MID=D.MID
WHERE M.MID<>0 AND M2.MID<>0 )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources