SQL sub-query troubles

[USX01]

I have stared at this for a while to no avail. I'm not sure why this isn't working,the errror i'm getting is a "invalid sql statement" error. Here's the rough SQL statement:

select A, B
from X
where A,B in (select Y,Z
from C
where ....)

Thanks inadvance

 

[MacLeod72]

I believe you need:

SELECT
    A,
    B
FROM
    X
WHERE
    (A, B) in (SELECT A, B FROM C WHERE)

--Rob

 

[USX01]

Thanks Rob, however in the where clause

WHERE
(A, B) in (SELECT A, B FROM C WHERE)

NEED TO BE:

SELECT
A,
B
FROM
X
WHERE
(A, B) in (SELECT Y,Z FROM C WHERE)

 

[SantaMufasa]

USX,

As an alternative and a performance comparison, try:

SELECT A,B
FROM X
WHERE exists (select 'foo' from C
               WHERE x.a = Y
                 and x.b = Z);

This construction typically performs much faster, producing the same results.

Let us know your findings.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]