CODEWITHSUNDEEP
sql-server-2005
priyanka.sarkar
priyanka.sarkar

Reputation: 26498

SQL Server 2005 query issues

Suppose I have a table like this..

ColA    ColB    ColC
----------------------
A       100     1
A       200     2
A       300     3
B       100     1
B       200     2
C       300     1

I have to select COLA where 100=1 and 200=2 and 300=3

Please help

Upvotes: 0

Views: 118

Answers (3)

AviD
AviD

Reputation: 13112

From what I could understand from your question, you need somthing simple like:

select COLA where ( (COLB=100 and COLC=1) or (COLB=200 and COLC=2) or (COLB=300 and COLC=3) )

or go for the more general:

select COLA where COLB = COLC*100

Upvotes: 3

artlung
artlung

Reputation: 34013

SELECT ColA
FROM   my_table
WHERE  ColB = (ColC * 100);

Upvotes: 2

Aaron Alton
Aaron Alton

Reputation: 23226

There are a few ways to handle that, but this is probably the simplest, and will perform quite decently given your alternatives:

SELECT ColA
FROM  MyTable
WHERE (ColB = 100 AND ColC = 1)
OR (ColB = 200 AND ColC = 2)
OR (ColB = 300 AND ColC = 3)
GROUP BY ColA
HAVING COUNT(*) = 3

Upvotes: 0

Related Questions