MySQLの「多対多」の絞り込みで、重複しないレコードを取得したい

Question

絞り込みの検索機能を実装中で悩んでおります。
例えば品物とカテゴリーの中間テーブルがあるとします。

itemsテーブル
|id|title|
|:-|:--:|
|1|シーフードカレー|
|2|肉じゃが|
|3|品物C|

category_itemテーブル
|id|item_id|category_id|
|:--|:--:|--:|
|1|1|1|
|2|1|2|
|3|1|3|
|4|2|1|
|5|2|2|

categoriesテーブル
|id|category_name|
|:-|:--:|
|1|肉類|
|2|野菜類|
|3|魚介類|

上記をJOINしてくると以下のような結果になるかと思います。
```sql
SELECT i.id, i.title, ci.category_id FROM items AS i INNER JOIN category_item AS ci ON i.id = ci.item_id
```
|id|title|category_id|
|:--|:--:|--:|
|1|シーフードカレー|1|
|1|シーフードカレー|2|
|1|シーフードカレー|3|
|2|肉じゃが|1|
|2|肉じゃが|2|

やりたいことはitemsのidを重複しない取得をしたいです。
|id|title|category_id|
|:--|:--:|--:|
|1|シーフードカレー|1|
|1|肉じゃが|1|

GROUP BYなどでグルーピングする方法も調べたら出てきたのですが、
このほかにも複数のテーブルをJOINしたり取得するカラムも別テーブルの項目を取得したいためGROUP BYを使用するとエラーになりうまくいきませんでした。

またサブクエリの使い方に関しても調べると出てくるのですが該当の取得方法をしたい時の書き方が分かりませんでした。

良き方法ご存知でしたらご教授いただきたいです。
よろしくお願いします。

**補足**
MySQL5.7になります。
タイトルを調整いたしました。
SELECT文が間違えていたので修正しました。
カテゴリー名が分かりづらかったので修正しました

**補足2**
通常であれば、絞り込み検索で検索されない限り
```sql
SELECT i.id, i.title FROM items AS i ORDER BY i.id DESC;
```
上記のようなSELECT文で一覧を出力させますが、絞り込み検索でチェックボックスでカテゴリーを選択した際に、
```sql
SELECT 
    i.id, i.title, ci.category_id 
FROM 
    items AS i 
INNER JOIN 
    category_item AS ci ON i.id = ci.item_id
WHERE 
    ci.category_id = 2
OR 
    ci.category_id = 1
ORDER BY i.id DESC
```
上記のようにSQL(当初考えた)が変化します。
複数の条件にマッチした同様のアイテム情報がレコードに出てきてしまうので

|id|title|category_id|
|:--|:--:|--:|
|2|肉じゃが|1|
|2|肉じゃが|2|
|1|シーフードカレー|1|
|1|シーフードカレー|2|

これを

|id|title|category_id|
|:--|:--:|--:|
|1|肉じゃが|1|
|2|シーフードカレー|1|

このようにできて一覧表示用の重複のないデータにできないかと考えておりました。
説明わかりづらく大変申し訳ありません。

Accepted Answer

いろいろ不明ですが、想像を膨らませて回答しておきます。想像が違ったらどこがどう違うかを下のコメント欄に書いてください。

以下のような**複数**（**複数というところがポイント**なのだろうと想像しました）のカテゴリーの条件で items テーブルにクエリを投げて、

```
WHERE ci.category_id = 2 OR ci.category_id = 1
```

条件に合う id, title, category_id を取得しているようですが、

> やりたいことはitemsのidを重複しない取得をしたいです。

という時点で category_id を取得するデータに含めるのは意味がなさそうです。であれば、SELECT に含めるのは id, title のみにしてはいかがですか？　そうすれば DISTINCT で重複しない id, title を取得できるはずです。

以下の画像は、Microsoft が提供しているサンプルデータベース Northwind の一部のテーブルです。Products が items、Order_Details が category_item、Orders が categories に該当すると思ってください。

![イメージ説明](https://ddjkaamml8q8x.cloudfront.net/questions/2022-04-12/7b98979e-c48f-4ca7-a871-7a925f14d7a0.jpeg)

以下は質問と同等のクエリですが、この例でも Chai が重複しています。SELECT に o.[OrderID] (質問者さんの場合は ci.category_id) が含まれているので DISTINCT で重複を排除することはできません。それ以外の方法があるかもしれませんが容易ではなさそうです。

![イメージ説明](https://ddjkaamml8q8x.cloudfront.net/questions/2022-04-12/8ecc4ccb-479d-48c0-b6e8-f024a78d9f39.jpeg)

でも「itemsのidを重複しない取得をしたい」と言った時点で、取得するデータに ci.category_id は不用ではないですか？　であれば、以下のように SELECT から o.[OrderID] (質問者さんの場合は ci.category_id) を削除して DISTINCT を付与すれば良さそうです。

![イメージ説明](https://ddjkaamml8q8x.cloudfront.net/questions/2022-04-12/31d5268c-4218-417c-9c82-0a33e512fa25.jpeg)

上の SQL は SQL Server のものですが、MySQL でもほぼ同じようにしてできると思います。お試しください。

Answer

細かいことはわかりませんが「検索条件列を得るために安易にjoin」はおすすめしません。
exists使いましょう。

またcategory_idをselectする必要はないと思います。どうしてもcategory_idが必要な場合は
「集計して全件出す」や「最小(最大)のcategory_id１件を出す」等
複数あることを前提に何を表示するかしっかり決めましょう。

```SQL
SELECT 
    i.id, i.title
FROM 
    items AS i 
WHERE exists
  (select 0
    from category_item AS ci
    where ci.item_id=i.id
　　and  (
        ci.category_id = 2
          OR 
        ci.category_id = 1
      )
)

ORDER BY i.id DESC
```

Answer

# 修正版
```SQL
create table items(id int primary key,title varchar(10));
insert into items values
(1,'シーフードカレー'),
(2,'肉じゃが'),
(3,'品物C');

create table category_item(id int primary key,item_id int,category_id int,unique(item_id,category_id));
insert into category_item values
(1,1,1),
(2,1,2),
(3,1,3),
(4,2,1),
(5,2,2);

create table categories(id int primary key,category_name varchar(10));
insert into categories values
(1,'肉類'),
(2,'野菜類'),
(3,'魚介類');
```
抽出
```SQL
select * from items as i
inner join(
select item_id as id,min(category_id) as category_id from category_item
group by item_id
) as tmp using(id)
```

id	title	category_id
1	シーフードカレー	1
1	シーフードカレー	2
1	シーフードカレー	3
2	肉じゃが	1
2	肉じゃが	2

id	category_name
1	肉類
2	野菜類
3	魚介類

修正版

関連した質問