Using "or" ,"and", "like" several times in same column

Question

If I have a table like this:

id     car
1       A
1       B
1       C
1       D
2       A
2       B
2       C
2       F
3       A
3       C
3       E
3       F
3       G

what I want is different "id" which have ("A" or "C") and "B" in car. For example:

id    car
1      A
1      B
1      C
2      A
2      B
2      C

what I did was

select * from table where (car like "A" or car like"C") and (car like "B")

but it gives me an empty row.

Any clue?

Answer 1

You can use a self-join

SELECT t1.id
FROM yourTable AS t1
JOIN yourTable AS t2 ON t1.id = t2.id
WHERE t1.car IN ('A', 'C')
AND t2.car = 'B'

BTW, you should generally only use LIKE when you're doing a pattern match. For exact matches use =, or IN for matching any of multiple items.