正規化による1つのレコードに対して複数のレコードがあるときのSELECTについて

Question

お世話になっております。
表題について質問させてください。

https://teratail.com/questions/174882

上記は他の方の質問ですが、こちらは1つのカラムに複数の値を文字列として登録されていますが、以前、正規化した方が良いとアドバイスを受け以下のような構成としています。

memberテーブル
no　　name　　sex
1　　佐藤　　man
2　　伊東　　woman
3　　金井　　man
4　　渡辺　　woman

like_dataテーブル
data_no data_name
1　　　　いちご
2　　　　みかｎ
3　　　　すいか
4　　　　ぶどう
5　　　　レモン
6　　　　バナナ

like_memberテーブル（上記テーブルを基にしたメンバーが好きなものリスト）
like_no　like_member_no　like_data_no
1　　　　1　　　　　　　　2
2　　　　2　　　　　　　　2
3　　　　1　　　　　　　　3
4　　　　3　　　　　　　　3
5　　　　1　　　　　　　　4
6　　　　2　　　　　　　　4
7　　　　3　　　　　　　　4
8　　　　4　　　　　　　　1
9　　　　4　　　　　　　　2
10　　　 4　　　　　　　　4

※like_member_noは、memberテーブルのnoと紐づく
※like_data_noは、like_dataテーブルのdata_noと紐づく

ここで質問ですが、性別だけを検索するとき、果物だけを検索するとき、性別と果物と併せて検索するときがありますが、どのようなSQLにしたら良いのでしょうか？（PHPでMySQLを操作しており、検索条件によってwhere文などを変更している）

例えば,、性別をman、果物Noを2とした場合、

佐藤と1行だけとしたいのですが、佐藤として登録のあるlike_memberのレコード数分（3行）が表示されてしまいます。

お恥ずかしいとは思いいますが、以下は現在のSQLです。

お忙しい中恐縮ですが、アドバイスのほどよろしくお願いいたします。

SELECT 
member.no, 
member.name, 
member.sex, 
like_member.like_member_no, 
like_member.like_data_no, 
like_data.data_no, 
like_data.data_name 
FROM member 
LEFT JOIN like_member ON member.no = like_member.like_member_no 
LEFT JOIN like_data ON like_member.like_data_no = like_data.data_no 
WHERE member.sex = 'man' 
AND EXISTS(SELECT * FROM 
like_member AS LM 
INNER JOIN like_data AS LD 
ON LM.like_data_no = LD.data_no 
WHERE member.no = LM.like_member_no AND 
data_no = '2') 

上記質問のテーブルのSQLは以下の通り

CREATE TABLE `like_data` (
  `data_no` int(11) NOT NULL,
  `data_name` varchar(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `like_data` (`data_no`, `data_name`) VALUES
(1, 'いちご'),
(2, 'みかん'),
(3, 'すいか'),
(4, 'ぶどう'),
(5, 'レモン'),
(6, 'バナナ');



CREATE TABLE `like_member` (
  `like_no` int(11) NOT NULL,
  `like_member_no` int(11) NOT NULL,
  `like_data_no` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `like_member` (`like_no`, `like_member_no`, `like_data_no`) VALUES
(1, 1, 2),
(2, 2, 2),
(3, 1, 3),
(4, 3, 3),
(5, 1, 4),
(6, 2, 4);



CREATE TABLE `member` (
  `no` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `sex` varchar(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `member` (`no`, `name`, `sex`) VALUES
(1, '佐藤', 'man'),
(2, '伊東', 'man'),
(3, '金井', 'man'),
(4, '渡辺', 'man');


ALTER TABLE `like_data`
  ADD PRIMARY KEY (`data_no`);


ALTER TABLE `like_member`
  ADD PRIMARY KEY (`like_no`);


ALTER TABLE `member`
  ADD PRIMARY KEY (`no`);



ALTER TABLE `like_data`
  MODIFY `data_no` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;


ALTER TABLE `like_member`
  MODIFY `like_no` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;


ALTER TABLE `member`
  MODIFY `no` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

Comments

[Eggpan]

実際のプログラムがPHPだからかなと思いますが、SQLのみの話ですので、質問内容からしてタグ「PHP 」は不要かと思います。

Answer 1

LEFT JOINの部分をINNER JOINに変更すれば目的のレコードは取得できると思います。
誤りでした。すみません。JOINしたら結局レコードはふえますね。

・JOINはせず、memberテーブルだけSELECTする
・@sola-msrさんのおっしゃっているようにGROUP BYで集約する
といった方法があるかと思います。

質問に書かれているSQLですと
・性別がman、果物Noが2である、佐藤
・上記の好きなものリスト
という検索になっていますので、みかん、すいか、ぶどうの3レコードが抽出されています。

Comments

[chapp]

Eggpanさん

貴重なご意見ありがとうございます。Eggpanさんの書込みを気付かず終了としてしまいました。失礼いたしました。

LEFT JOINをINNER JOINに変えて試していますが、記述がおかしいのか、期待している結果を求めることが出来ません。LEFT JOINをINNER JOINに変える他、どこか直す場所はありますでしょうか・・

[Eggpan]

すみません、勘違いしていました。

1行しか出力したくない、といういうことは、果物は2だけ出したい、ということかと思いますので、
AND EXISTSの ...部分を全部削除して、代わりに
AND like_data.data_no = '2' とし、かつ、
「果物が検索された場合のみ」JOINとWHERE句へのlike_data = 'xx'を追加する

といった処理が必要そうですね。
3レコード抽出の原因は合っているかと思うのですが、対応としてINNER JOINは誤りなので、のちほど回答を修正します。

[chapp]

Eggpanさん

早速のお返事、ありがとうございます！
こちらの説明は上手くできずに誤解させてしまったようで、こちらこそすみませんでした。

AND EXISTSの部分、見直してみたいと思います。
終了後にも関わらず、ご親切な対応をありがとうございました！

Answer 2

GROUP BY like_member.like_member_no

GROUP BY句で集約すればいけそうなきが

Comments

[chapp]

sola_msrさん

早速の回答、ありがとうございます。
仰る通りですね。応用が出来ていませんでした。
アドバイス、ありがとうございました！