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

Question

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.

Your second query is perfect. It returns three rows. What's the problem now? — Himanshu
– Himanshu, Commented Jul 26, 2013 at 9:43
Strange. I'm using HeidiSQL and it's giving me just one result. Maybe there's some differences between HeidiSQL and normal MySQL? — ehz350
– ehz350, Commented Jul 26, 2013 at 9:47
HeidiSQL is just another MySQL client. There is no reason for it to return different results for the same query on the same server. — Vatev
– Vatev, Commented Jul 26, 2013 at 10:19

peterm · Accepted Answer · 2013-07-26 09:52:23Z

8

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

edited Jul 26, 2013 at 9:52

answered Jul 26, 2013 at 9:43

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Himanshu Over a year ago

This is the perfect query. But he has already tired the same. Don't know why he is not getting expected result.

Joachim Isaksson Over a year ago

@Rayf Nope, the original query works perfectly.

Horsemouth · Accepted Answer · 2013-07-26 09:51:35Z

0

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?

answered Jul 26, 2013 at 9:51

Horsemouth

912 silver badges3 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related