MySQL query with multiple AND statement

Question

I have database table like this:

|id |item    |
|1  |item-1  |
|2  |item-1  |
|1  |item-2  |
|3  |item-3  |
|2  |item-2  |
|1  |item-3  |

How in MySQL get id who hes item-1 AND item-2?

This don't work:

SELECT id FROM table WHERE item = 'item-1' AND item = 'item-2'

I know why it doesn't work, but how to get a result like this:

|id |
|1  |
|2  |

@RiggsFolly I think the intention is to get `id` values which have both items, not either item. — Bill Karwin, Sep 06 '21 at 17:26
WHERE item IN ('item-1', 'item-2') - That I use now but that is not what I need. Thanks — Djuka, Sep 06 '21 at 17:27
You can also click the [tag:relational-division] tag to see other solutions. — Bill Karwin, Sep 06 '21 at 17:31

score 0 · Answer 1 · edited Sep 06 '21 at 17:29

0

for every additional condition you can use EXISTS

SELECT id FROM table WHERE item = 'item-1' 
AND EXISTS (SELECT 1 FROM table  WHERE item = 'item-2')

edited Sep 06 '21 at 17:29

Kartoffelkultur

answered Sep 06 '21 at 17:25

nbk

1 Answers1