asked on

multiple column subquery

Greetings,

Can someone point me to an equivalent statement that will run on both
ms sql server 7 and 2000?

I am trying to select some rows from a table where two column entries
match some other criteria. The Oracle equivalent is at the bottom of
this message... In MS SQL Server Query Analyzer I am getting errors
trying this:

SELECT table1.col1, table1.col2, table1.col3
FROM table1
WHERE (table1.num, table1.code) in
(SELECT table2.num, table2.code from table2
where table2.col4 = value

The error is: "Incorrect Syntax near ',' ", so it seems to not like
the comma in "where (table1,num, table2.code) in"

The important thing to remember is that each row in one table will have the same two values as a row in the other table.

.. I did not want to concatenate the rows as it seems to deliver different results.

Thanks for any assistance,
Jeff

select (col1, col2, col3
from table1
where (num, code) in
(select num, code from table2 where col4 = value);

ASKER CERTIFIED SOLUTION

trouta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

You can also use a join to resolve this, like so:

SELECT table1.col1, table1.col2, table1.col3
FROM table1
INNER JOIN table2 ON table1.num = table2.num AND table1.code = table2.code
WHERE table2.col4 = value

usmanr

extending trouta's solution, you can also write it this way,,
select * from Address where exists (select 1 where City = 'Bothell' and AddressLine1 = '1970 Napa Ct.')