SQL Server Performance

multiple para in "IN" clause

Discussion in 'General Developer Questions' started by gtbuzz, Aug 12, 2003.

  1. gtbuzz New Member

    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.
  2. gaurav_bindlish New Member

    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
  3. Luis Martin Moderator

    IN clause does not allow more than one argument.

    SQL is not Oracle, sorry.

    Luis Martin
  4. rushmada New Member

    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
  5. Twan New Member

    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

  6. bambola New Member

    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.
  7. gtbuzz New Member

    Thanks for the info.

Share This Page