how to subquery with 2 or multiple columns

This is a small problem .. hope I could find quick answer :)
I am trying to write a query which uses a subquery in its where
condition. there are 2 columns (both PK) for which checking is
required.
I can run this query in DB2 with no problems. what is the correct
approach to do that in SQL Server 2005 ?
SELECT EMP_ID,DEPT_ID
FROM
STAGE_RMRS_PPLSFT
WHERE
(EMP_ID,DEPT_ID)
NOT IN
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM
STAGE_RMRS_PPLSFT
)
Note : I did managed to do the same in SQL with combining the 2
columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
as index scan doesn't apply here.
pls suggest me a better solutionAs you've noticed, SQL Server doesn't allow multiple columns in IN (row-valued constructors). I
suggest you use EXISTS instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution|||"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution
Try one of the following. There is a difference however. Your query would
return no rows at all if either of MANAGER_ID or MGR_DEPT_ID contain any
nulls. Very often that is not a desirable result so the two alternatives
I've given here might be suitable.
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT
EXCEPT
SELECT MANAGER_ID, MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT;
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT S
WHERE NOT EXISTS
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT
WHERE MANAGER_ID = S.EMP_ID
AND MGR_DEPT_ID = S.DEPT_ID);
Going by the column names alone I would guess this table is not well
normalized (MANAGER_ID->MGR_DEPT_ID?), which is something you should
probably consider fixing.
HTH
--
David Portas|||will use the correlated query with EXIST clause ..
Thanks a lot for your Help.. :)