left outer join について質問です。

Question

###前提・実現したいこと
php+mysqlでウェブアプリを作っている中で、
mysql をDBのセレクト文について、はまってしまい。
質問させていただきました。
left outer join についてです。

下記のような時にA.s_id=100とAテーブルの5つを指定しているのですが、

結果が、
初心者の筆問で、うまく意図が伝わっているのかわかりませんが、レフトジョインなので、
左にならえということで、
whereでAテーブルのs_idが入った5個が抽出されると思っておりましたが結果が
共通ID(A_id)を持ったものすべてが抽出されており、困惑しております。

下記の事で、
結合して10個の結果全てに対してs_idが入って結合したものでwhereしているものと考えてよろしいでしょうか。
その時主となるテーブルAに該当するものが5個しかなくても、それよりも多く抽出しているのか不可解でご教授頂きたいです。

また、下記のような結果を取得したい場合どのように書くことができるのかご指導いただければ幸いです。
【取りたいデーター】

|A_id|担当者|B_id|
|:--|:--:|--:|
|1|aさん|1||
|2|bさん|2||
|3|cさん|3||
|4|dさん|4||
|5|eさん|5||

Aテーブルにある数のみ重複はさせたくない。


###発生している問題・エラーメッセージ

テーブル名A
|A_id|名前|年齢|s_id|
|:--|:--:|--:|
|1|Aさん|25|100|
|2|Bさん|22|100|
|3|Cさん|23|100|
|4|Dさん|22|100|
|5|Eさん|24|100|
|6|Fさん|24|200|

テーブル名B
###【訂正しました】B_idとA_idが逆に記載しておりました・・・
|B_id|担当者|A_id|
|:--|:--:|--:|
|1|aさん|1||
|2|bさん|2||
|3|cさん|3||
|4|dさん|4||
|5|eさん|5||
|6|fさん|1||
|7|gさん|2||
|8|hさん|3||
|9|iさん|4||
|10|jさん|5||

###該当のソースコード
使用したmql文
SELECT A.A_id,A.名前,B.B_id FROM A LEFT OUTER JOIN B on A.A_id = B.A_id where A.s_id = 100;

###試したこと
結果が、
|A_id|名前|B_id|
|:--|:--:|--:|
|1|Aさん|1|
|2|Bさん|2|
|3|Cさん|3|
|4|Dさん|4|
|5|Eさん|5|
|1|Aさん|1|
|2|Bさん|2|
|3|Cさん|3|
|4|Dさん|4|
|5|Eさん|5|

全部で10個がでてしまいます。
私的には、whereでAテーブルの5個を指定しているので、5個しか出ないと思っていたのですが・・・


###補足情報(言語/FW/ツール等のバージョンなど)
より詳細な情報
環境は、
php 5.6
mysql 5.6
になります。
どうぞよろしくお願いいたします。

Accepted Answer

結合すると多い件数の方で結果が返却されると考えてください。
Aテーブルに対して、複数人の担当者がいるので当然Aテーブルの件数より多い結果になります。

Aテーブルを軸にAテーブルの件数のみとしたい場合には、複数の担当者を1件にする必要があります。
そうすると担当者のうち誰を１件とするかの条件を決めなければなりません。

以下は、担当者のID（B_ID）が一番小さい人の場合です。
```SQL
SELECT A.A_id,A.名前,fil_B.B_id 
FROM A LEFT OUTER JOIN (
  select * from B as main_B
  where B_ID = (select min(B_ID) from B where A_ID=main_B.A_ID)
 ) as fil_B
 on A.A_id = fil_B.A_id 
where A.s_id = 100
;
```
別解
---
s_idがテーブルA全体に対して小さな分布であるなら、以下の方が高速かもしれません。
```SQL
SELECT A_id, 名前, (select min(B_id) from B where A_ID=A.A_ID) as B_id
FROM A 
where s_id = 100
;
```

Answer

単純な話、テーブルBを絞り込む条件が足りないからです。
B_idがかぶった場合、最も小さなA_idを選ぶという条件を追加すればすんなりでます
（訂正：A_idがかぶった場合、最も小さなB_idを選ぶ・・・が正しいです、すみません）
- 元データ
```SQL
create table tbl_a(A_id int unique,name varchar(20),age int,s_id int);
insert into tbl_a values
(1,'Aさん',25,100),
(2,'Bさん',22,100),
(3,'Cさん',23,100),
(4,'Dさん',22,100),
(5,'Eさん',24,100),
(6,'Fさん',24,200);

create table tbl_b (B_id int unique,tanto varchar(20),A_id int);
insert into tbl_b values
(1,'aさん',1),
(2,'bさん',2),
(3,'cさん',3),
(4,'dさん',4),
(5,'eさん',5),
(6,'fさん',1),
(7,'gさん',2),
(8,'hさん',3),
(9,'iさん',4),
(10,'jさん',5);

```
- 検索
```SQL
select * from tbl_a as t1
left join 
tbl_b as t2  on t1.A_id=t2.A_id
and exists(select 1 from tbl_b as t3 where t2.A_id=t3.A_id and t2.B_id<t3.B_id)
where t1.s_id=100
```

Answer

where句で対象となるのは5件ですが
結合条件（on句）で当てはまるものが複数あればその分は増えます。
でないと、どちらを結合すれば良いか分かりませんし
複数引っ掛かれば、複数を結合し、2行出るのは然るべきことです。

結果を見るとwhere句は問題なく、on句がおかしいのではないですか？
`SELECT A.A_id,A.名前,B.B_id FROM A LEFT OUTER JOIN B on A.A_id = B.A_id where A.s_id = 100;`
と載せていただいていますが
結果からみると`on A.A_id = B.B_id`になっているように思えるのですが・・・

`SELECT A.A_id,A.名前,B.B_id`の部分を`SELECT A.A_id,B.名前,B.B_id`にしたら
fさんとかgさんとか出て来ませんか？

on句を見直してみていただければと思います。

Answer

やりたいことがイメージできていれば、ツールを使用することでテンプレートが簡単に作れます。
[SQL Joins Visualizer](http://sql-joins.leopard.in.ua/)

B_id	担当者	A_id
1	aさん	1
2	bさん	2
3	cさん	3
4	dさん	4
5	eさん	5
6	fさん	1
7	gさん	2
8	hさん	3
9	iさん	4
10	jさん	5

A_id	名前	B_id
1	Aさん	1
2	Bさん	2
3	Cさん	3
4	Dさん	4
5	Eさん	5
1	Aさん	1
2	Bさん	2
3	Cさん	3
4	Dさん	4
5	Eさん	5

別解

関連した質問