How to use two keys in subquery

I can use a key in subquery, like
Select * from Employees where key1 in (Select key1,from SalesPerson)
But if I have two keys like
Select * from Employees where key1 and key2 in (Select key1, key2 from
SalesPerson..)
The above is wrong, but how can I do it?
Use EXISTS:
SELECT *
FROM employees AS e
WHERE EXISTS
(SELECT *
FROM salesperson AS P
WHERE P.key1 = E.key1
AND P.key2 = E.key2) ;
Or possibly using a join:
SELECT *
FROM employees AS e
JOIN salesperson AS P
ON P.key1 = E.key1
AND P.key2 = E.key2 ;
David Portas
SQL Server MVP

How to use two keys in subquery

I can use a key in subquery, like
Select * from Employees where key1 in (Select key1,from SalesPerson)
But if I have two keys like
Select * from Employees where key1 and key2 in (Select key1, key2 from
SalesPerson..)
The above is wrong, but how can I do it?Use EXISTS:
SELECT *
FROM employees AS e
WHERE EXISTS
(SELECT *
FROM salesperson AS P
WHERE P.key1 = E.key1
AND P.key2 = E.key2) ;
Or possibly using a join:
SELECT *
FROM employees AS e
JOIN salesperson AS P
ON P.key1 = E.key1
AND P.key2 = E.key2 ;
David Portas
SQL Server MVP
--

How to use two keys in subquery

I can use a key in subquery, like
Select * from Employees where key1 in (Select key1,from SalesPerson)
But if I have two keys like
Select * from Employees where key1 and key2 in (Select key1, key2 from
SalesPerson..)
The above is wrong, but how can I do it?Use EXISTS:
SELECT *
FROM employees AS e
WHERE EXISTS
(SELECT *
FROM salesperson AS P
WHERE P.key1 = E.key1
AND P.key2 = E.key2) ;
Or possibly using a join:
SELECT *
FROM employees AS e
JOIN salesperson AS P
ON P.key1 = E.key1
AND P.key2 = E.key2 ;
--
David Portas
SQL Server MVP
--

How to use parameterized UDF in a join query?

I have a table of employees and a User Defined Function udf_GetEmpQualifiedUnits which takes an employee ID and returns all the Units in which that employee can work. I want to get all the employees who are active and their qualified units. The UDF returns EmpID and UnitID (EmpID is same as given as parameter).

Now when I execute the following script;

Code Snippet

select distinct E.EmpID, EU.UnitID

from tblEmp E

inner join udf_GetEmpQualifiedUnits(E.EmpID) EU on E.EmpID = EU.EmpID and E.EmpActiveFlg <> 0

I get this error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "E.EmpID" could not be bound.

I do not want to use Cursors/loops as I am having the same above problem in many of my queries.

It is quite possible that I am unaware of the syntax or other things that could solve my problem.

Thank you.

You can't do this..Rewrite your logic of udf_GetEmpQualifiedUnits on your join query.

|||You can do this if you are using 2005...you will need to use the APPLY operator rather than a join operator. Heres an article I wrote a while back that explains how it works:
http://articles.techrepublic.com.com/5100-9592-6108869.html

Tim