August 2, 2002 at 7:17 am
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.
August 2, 2002 at 7:50 am
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
HTH,
Vyas
SQL Server MVP
http://vyaskn.tripod.com/
August 2, 2002 at 7:59 am
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)
August 2, 2002 at 8:09 am
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.
August 3, 2002 at 1:23 am
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