0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

1対多のテーブルで、多の方を絞り込み条件とする

Posted at

ユーザーテーブルとユーザー属性テーブルが存在し、属性を条件としたユーザーの絞り込みを行いたい場合の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
);
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?