SQL query Impossibile WHERE with two condition

Question

I am trying to execute this query:

SELECT * FROM person JOIN colors ON person.IDA=colors.IDA where Age=20 AND (Color="Black" AND Color="Orange")

So I need a joined table with all the rows that have

person.Age = 20
colors.Color = Black
colors.Color = Orange

I can query correctly only with one condition

SELECT * from person join colors on person.IDA = colors.IDA WHERE Age=20 and colors.Color="Black"

(result)

+-----+------+-----+------+------+-------+
| IDA | Name | Age | ID   | IDA  | Color |
+-----+------+-----+------+------+-------+
|   1 | John |  20 |    4 |    1 | Black |
|   3 | Bob  |  20 |   13 |    3 | Black |
|   3 | Bob  |  20 |   16 |    3 | Black |
+-----+------+-----+------+------+-------+

So I'm expecting a result like that when (Color="Black" AND Color="Orange"):

+-----+------+-----+------+------+--------+
| IDA | Name | Age | ID   | IDA  | Color  |
+-----+------+-----+------+------+--------+
|   1 | John |  20 |    3 |    1 | Orange |
|   1 | John |  20 |    4 |    1 | Black  |
|   3 | Bob  |  20 |   11 |    3 | Orange |
|   3 | Bob  |  20 |   13 |    3 | Black  |
+-----+------+-----+------+------+--------+

But I get Impossible WHERE when EXPLAIN the query.

DUMP:

person:

+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| IDA   | int(50)  | NO   | PRI | NULL    | auto_increment |
| Name  | tinytext | NO   |     | NULL    |                |
| Age   | int(50)  | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

+-----+------+-----+
| IDA | Name | Age |
+-----+------+-----+
|   1 | John |  20 |
|   2 | Alex |  21 |
|   3 | Bob  |  20 |
+-----+------+-----+
3 rows in set (0.00 sec)

color

+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| ID    | int(50)  | NO   | PRI | NULL    | auto_increment |
| IDA   | int(50)  | NO   |     | NULL    |                |
| Color | tinytext | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

+----+-----+--------+
| ID | IDA | Color  |
+----+-----+--------+
|  1 |   1 | White  |
|  2 |   1 | White  |
|  3 |   1 | Orange |
|  4 |   1 | Black  |
|  5 |   2 | Black  |
|  6 |   2 | Black  |
|  7 |   2 | Black  |
|  8 |   2 | Black  |
|  9 |   2 | Orange |
| 10 |   2 | Yellow |
| 11 |   3 | Orange |
| 12 |   3 | White  |
| 13 |   3 | Black  |
| 14 |   3 | Yellow |
| 15 |   1 | Orange |
| 16 |   3 | Black  |
+----+-----+--------+
16 rows in set (0.00 sec)

EDIT:

Thank you for the answers, I think I've picked the wrong query: I'm looking for a result that have BOTH Black and Orange. If I substitute Black with White:

SELECT * FROM person JOIN colors ON person.IDA=colors.IDA where Color="White" OR  Color="Orange"

I will get

|   2 | Alex |  21 |  9 |   2 | Orange |

Alex have only the color Orange, not Orange and White

+-----+------+-----+----+-----+--------+
| IDA | Name | Age | ID | IDA | Color  |
+-----+------+-----+----+-----+--------+
|   1 | John |  20 |  1 |   1 | White  |
|   1 | John |  20 |  2 |   1 | White  |
|   1 | John |  20 |  3 |   1 | Orange |
|   2 | Alex |  21 |  9 |   2 | Orange |
|   3 | Bob  |  20 | 11 |   3 | Orange |
|   3 | Bob  |  20 | 12 |   3 | White  |
|   1 | John |  20 | 15 |   1 | Orange |
+-----+------+-----+----+-----+--------+

The suggested way of doing this in MySQL is usually to have an additional join for each condition, see http://stackoverflow.com/questions/2300322/intersect-in-mysql — TZHX, Mar 21 '17 at 20:36

score 0 · Answer 1 · answered Mar 21 '17 at 19:24

0

You need to use "OR":

SELECT * FROM person
JOIN colors ON person.IDA=colors.IDA
where person.Age=20 AND (colors.Color="Black" OR colors.Color="Orange")

answered Mar 21 '17 at 19:24

Don Rhummy

24,730
42
175
330

score 0 · Answer 2 · answered Mar 21 '17 at 19:24

0

Most likely, you're looking for something such as this (i.e. basically an OR condition):

SELECT * from person join colors 
    on person.IDA = colors.IDA 
 WHERE Age=20 and (colors.Color="Black" or colors.Color="Orange")

answered Mar 21 '17 at 19:24

Dhruv Saxena

1,336
2
12
29

score 0 · Answer 3 · answered Mar 21 '17 at 19:25

0

SELECT * FROM person JOIN colors ON person.IDA=colors.IDA where Age=20 AND (Color="Black" AND Color="Orange").

You need to use (Color="Black" OR Color="Orange") instead of (Color="Black" AND Color="Orange")

answered Mar 21 '17 at 19:25

melihceyhan

42
4

score 0 · Answer 4 · answered Mar 21 '17 at 19:26

0

You need to change AND operator to OR.

WHERE Age=20 OR colors.Color="Black"

answered Mar 21 '17 at 19:26

tomaso

97
9

score 0 · Answer 5 · answered Mar 21 '17 at 19:27

0

Are you looking for results from both Black and Orange. If so, use the OR operator instead of AND.

answered Mar 21 '17 at 19:27

Tyler Davis

1
1

score 0 · Answer 6 · answered Mar 21 '17 at 19:27

The WHERE is impossible because you are requiring Color to be both black and orange at the same time.

You can use OR instead of AND to search for either color.

SELECT * FROM person JOIN colors
  ON person.IDA=colors.IDA
  WHERE
    Age=20 AND
    (Color="Black" OR Color="Orange")

SQL query Impossibile WHERE with two condition

6 Answers6