CODEWITHSUNDEEP
mysql
ehz350
ehz350

Reputation: 73

How to select rows with multiple specific column values in single query?

Table A

itemNo   colorNo
1        3
1        4
2        4
2        70
3        9
3        10

I wanted to do this...

SELECT *
FROM A
WHERE itemNo = '1' AND colorNo = '4';
SELECT *
FROM A
WHERE itemNo = '2' AND colorNo = '70';
SELECT *
FROM A
WHERE itemNo = '3' AND colorNo = '9';

But can I combine those 3 queries into one?

I tried to do this, but it only returned one row satisfying the last condition.

SELECT *
FROM A
WHERE ((itemNo = '1' AND colorNo = '4') 
or (itemNo = '2' AND colorNo = '70') 
or (itemNo = '3' AND colorNo = '9'));

EDIT: It turns out the table I got is faulty. The first two 'itemNo' didn't even exist! No wonder only the last one got returned. Thank you to everyone who helped! I'll leave this up here and hopefully it'll help someone with a similar question.

Upvotes: 1

Views: 1842

Answers (2)

peterm
peterm

Reputation: 92785

Try

SELECT *
  FROM A
 WHERE (itemNo = '1' AND colorNo =  '4')
    OR (itemNo = '2' AND colorNo = '70')
    OR (itemNo = '3' AND colorNo =  '9')

or you can also do this

SELECT *
  FROM A
 WHERE (itemNo, colorNo) IN ((1, 4),(2, 70),(3, 9))

Output:

| ITEMNO | COLORNO |
--------------------
|      1 |       4 |
|      2 |      70 |
|      3 |       9 |

Here is SQLFiddle demo

Upvotes: 8

Horsemouth
Horsemouth

Reputation: 91

SELECT 
    *
FROM 
    A
WHERE 
    (itemNo = '1' AND colorNo = '4') OR
    (itemNo = '2' AND colorNo = '70') OR
    (itemNo = '3' AND colorNo = '9');

is that what you mean?

Upvotes: 0

Related Questions