ユーザーテーブルとユーザー属性テーブルが存在し、属性を条件としたユーザーの絞り込みを行いたい場合のSQL
users
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
user_attributes
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | | NULL | |
| attr_key | varchar(20) | NO | | NULL | |
| attr_value | varchar(255) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
SELECT * FROM users
WHERE id IN (
SELECT user_id FROM user_attributes
WHERE 0 OR
(attr_key = 'blood_type' AND attr_value = 'A') OR
(attr_key = 'pref_code' AND attr_value = '13')
GROUP BY user_id
HAVING COUNT(*) = 2
);