multiple columns and IN

  • I have a table with two columns as primary key. I want to select rows from table based on the primary key. How can I do that using IN? I tried

    select * from table where (col1, col2) in

    ((val11, val12), (val21, val22))

    here col1 and col2 forms primary key.

    It's syntatically wrong. How can I present the primary key in this case?

    Thanx.

  • This syntax is covered by ANSI standard, but is not implemented by SQL Server yet. Use EXISTS instead. Here's an example:

    CREATE TABLE t1 (i int, j int)

    CREATE TABLE t2 (i int, j int)

    GO

    INSERT t1 SELECT 1, 1 UNION ALL SELECT 2, 2 UNION ALL SELECT 3, 3

    INSERT t2 SELECT 1, 1

    GO

    SELECT *

    FROM t2

    WHERE EXISTS

    (

    SELECT 1

    FROM t1

    WHERE t2.i = t1.i AND t2.j = t1.j

    )

    HTH,

    Vyas

    http://vyaskn.tripod.com/


    HTH,
    Vyas
    SQL Server MVP
    http://vyaskn.tripod.com/

  • I don't believe you can do this using the IN syntax. Two possibilities exist :

    1. try 'concatenating' the two values of your PK in a single value. For strings, it is obvious. For other types (eg. integers or datetimes) it gets more difficult.

    This yields (you might need to cast the literals).

    select * from table where

    cast(col1 as varchar) + cast(col2 as varchar) in

    ('val11+val12', 'val21+val22')

    2. Or you can use 'EXISTS'

    select * from table where EXISTS

    SELECT 1 WHERE

    (col1 = val11 and col2 = val12)

    OR (col1 = val21 and col2 = val22)

  • Your suggestion works only for subquery. What if I already know the keys? Of cause, one way to do it is to iterate over the keys in each select like

    foreach (key in keys)

    {

    val1 = key.col1;

    val2 = key.col2;

    querystring =

    "select * from table where col1 = " + val1.ToString() + " and col2 = " +val2.ToString();

    //ADO stuff...

    }

    This solution seesm less efficent. I think that there should be a way to do this in one query.

  • SELECT *

    FROM Table

    INNER JOIN

    (

    SELECT Val11 As Col1,

    Val12 As Col2

    UNION ALL

    SELECT Val21,

    Val22

    ) As RS ON Table.Col1 = RS.Col1 AND Table.Col2 = RS.Col2

    basically what vyas suggested but without the temp table and the exists clause!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply