如何在同一个表中对多个列值进行数据匹配[重复]

Question

This question already has an answer here:

• MySQL search products with their attributes 1 answer

How can I get the ID of those books which belong to country 79 AND author 80 AND publisher 91

SELECT id FROM books WHERE ( 
    (filter = 'country' AND cat = 79) 
        AND
    (filter = 'author' AND cat = 80)
        AND
    (filter = 'publisher' AND cat = 91)
    )   

Here is my table structure

+----+----------+-----+
| ID | FITER    | CAT |
+----+----------+-----+
|  1 | country  | 79  |
|  1 | author   | 80  |
|  1 | publisher| 91  |
|  2 | country  | 476 |
|  2 | author   | 85  |
|  2 | publisher| 121 |
+----+----------+-----+         

Thank you

</div>

Answer 1

This is the problem with this kind of table structure, do you really have that many filters that they can't be columns on their own?. Anyway, it would be like this:

SELECT DISTINCT id 
FROM books b
WHERE filter = 'country' AND cat = 79
AND EXISTS( SELECT 1 FROM books 
            WHERE filter = 'author' AND cat = 80
            AND id = b.id)
AND EXISTS( SELECT 1 FROM books 
            WHERE filter = 'publisher' AND cat = 91
            AND id = b.id);