multiple para in "IN" clause

Hi,

the following query fails due to syntax error around ",":
select cls_id, id, name from prog
where (cls_id, id) in
(
select distinct cls_id, id from
sch_prog
)

if only one para in "IN" clause, ie ... where cls_id in ...
it works fine.
Is it possible to include multiple para in "IN" clause in SQL Server?
(The query above works fine in oracle.)
Thanks in advance.

I think IN cluase does not allow more than one variable to compare
See if this works...
select cls_id, id, name from prog a
where exists in
(
select distinct cls_id, id from
sch_prog b
where a.cls_id = b.cls_id
and a.id = b.id
)


Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

By concatenating the both parameters we can get this.

For example:-

select cls_id, id, name from prog
where (ltrim(rtrim(cls_id))+ltrim(rtrim(id))) in
(
select distinct ltrim(rtrim(cls_id))+ltrim(rtrim(id)) from
sch_prog
)

Assuming that cls_id and id are treated character type columns.

Rushendra

alternatively, if p.cls_id and p.id are a unique key for prog then

select distinct
p.cls_id,
p.id,
p.name
from prog p
inner join sch_prog sp
on o.cls_id = sp.cls_id
and p.id = sp.id

or

select p.cls_id,
p.id,
p.name
from prog p
inner join ( select distinct cls_id, id from sch_prog ) sp
on o.cls_id = sp.cls_id
and p.id = sp.id

quote:Originally posted by rushmada
select cls_id, id, name from prog
where (ltrim(rtrim(cls_id))+ltrim(rtrim(id))) in
(
select distinct ltrim(rtrim(cls_id))+ltrim(rtrim(id)) from
sch_prog
)

Concating the strings + the ltrim rtrim functions will eliminate the
possible use of an index when there is one. In this case a join
seems to me a more efficient solution.

Bambola.

Thanks for the info.