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 NOT IN

Hi,

Im having a view(View1).Its unique keys are nvcrLocationCode, nvcrAssetCode.

Theres another table (Table1) thats also contain same unique keys.

I just want to filter records from View1 which are not in Table1.

Hint:

View1

nvcrLocationCode nvcrAssetCode

H001 A0001

W001 A0001

Table1

nvcrLocationCode nvcrAssetCode

H001 A0001

It should out put

W001 A0001

How do I do this

You could use EXCEPT operator (in SQL SERVER 2005 only) or NOT EXISTS clause:

Code Snippet

create table #View1

(

nvcrLocationCode nvarchar(4),

nvcrAssetCode nvarchar(5)

)

go

create table #Table1

(

nvcrLocationCode nvarchar(4),

nvcrAssetCode nvarchar(5)

)

go

select * fro

insert into #View1 values('H001', 'A0001')

insert into #View1 values('W001', 'A0001')

insert into #Table1 values('H001', 'A0001')

select * from #view1

EXCEPT

select * from #Table1

select * from #view1 v where NOT EXISTS

(

select * from #table1 t

where t.nvcrLocationCode=v.nvcrLocationCode and

t.nvcrAssetCode=v.nvcrAssetCode

)

|||you can also try this..

SELECT View1.*
FROM View1 LEFT OUTER JOIN
Table1 ON View1.nvcrLocationCode = Table1.nvcrLocationCode
AND View1.nvcrAssetCode = Table1.nvcrAssetCode
WHERE Table1.nvcrLocationCode IS NULL
AND Table1.nvcrAssetCode IS NULL