MySQL 特定のカラムの重複を避けてLEFT JOINで結合したい

Question

MySQL 特定のカラムの重複を避けてLEFT JOINで結合したいです。

結合したいテーブルは以下の二つです。

+----+-------------+----------+-----+
| id | Jananese    | English  | age |
+----+-------------+----------+-----+
|  1 | 犬          | dog      |   4 |
|  2 | 猿          | monkey   |   8 |
|  3 | 猫          | cat      |   1 |
|  4 | 鳥          | bird     |   2 |
|  5 | ライオン      | lion     |   9 |
+----+-------------+----------+-----+

+----+------------+-------------+------+
| id | animal_id  | staff_no    | sex  |
+----+------------+-------------+------+
|  1 |          1 | ueno_001    |    M |
|  2 |          2 | ueno_002    |    F |
|  3 |          1 | ueno_003    |    M |
|  4 |          3 | ueno_004    |    F |
|  5 |          3 | ueno_004    |    F |
+----+------------+-------------+------+

そして、以下のようなLEFT JOINを使って以下のような結果を求めています。

求める表示結果

+----+------------+----------+----------+
| id | staff_no   | Jananese | English  |
+----+------------+----------+----------+
|  1 | ueno_001   |       犬 |      dog |
|  2 | ueno_002   |       猿 |   monkey |
|  3 | ueno_003   |       犬 |      dog |
|  4 | ueno_004   |       猫 |      cat |
+----+------------+------- --+----------+

ですが、staff_numberの部分の重複を避けたいのですが、うまくできません。
現在のコードを出力結果は以下の通りです。

現在のコード

SELECT animal.id, staff_no, Jaopanese, English
  FROM staff
  LEFT JOIN animal
  ON staff.animal_id = animal.id ;

現在の結果

+----+------------+-------------+----------+
| id | staff_no   |   Jananese  | English  |
+----+------------+-------------+----------+
|  1 | ueno_001   |          犬 |      dog |
|  2 | ueno_002   |          猿 |   monkey |
|  3 | ueno_003   |          犬 |      dog |
|  4 | ueno_004   |          猫 |      cat |
|  5 | ueno_004   |          猫 |      cat |
+----+------------+-------------+----------+

staff_noのカラムの値の重複をさけ、ueno_004の表示を見本のように一回にしたいです。
distinctなども使ってみたのですが、うまく行きませんでした。
特定のカラムのみ重複を避けるにはどうしたら良いでしょうか？
アドバイスお願いいたします。

Comments

[YT0014]

staffテーブルで、同一staff_noで、別animal_idだった場合、どういう結果を想定されていますか？
例えば、以下の場合とか
| id | animal_id | staff_no | sex |
| 6 | 1 | ueno_005 | F |
| 7 | 2 | ueno_005 | M |

[退会済みユーザー]

ありがとうございます。正直なところ、求める結果の表示にとらわれ、同一staff_noで、別animal_idの場合を想定していなかったです。
同一staff_noで、別animal_idだった場合は、staff_noが二回表示されるようにしたいと思います。
各ナンバーのスタッフがお世話する動物が表示され、かつ、上から順に1,2,3,4...と、一番左のIDカラムに順に番号が割りふられるイメージです、

[shun-K]

なんか不思議なテーブル設計ですね。
animal=飼育している動物、staff=飼育員 ということでしょうか？
飼育員「ueno_004」は男性なのですか、女性なのですか？

[退会済みユーザー]

私のテーブル設計に問題がありました・・すみません。
「ueno_004」のsexをFに統一しました。
性別の設定はあまり意味はなく、LEFT JOINを使って、id | staff_no | Jananese | English 　のカラムを結合するという意図飲みで作ったテーブルでした。そのため、設定としてよくないテーブルになってしまいました。すみません。

Answer 1

staffテーブルに問題があります
id=4,5が同じデータです、
特定のanimal_idに対しておなじstaff_noを割り当ててはだめです。
普通はstaffテーブルに対してanial_idとstaff_noの複合カラムでユニーク属性をつけて
このような重複が入力できないようにします

またstaff_no単体で重複を許すのであればsexカラムは正規化して別テーブルで管理します

Comments

[退会済みユーザー]

ありがとうございます。ユニーク属性をつけるなど、データの重複を避ける方法について、理解できました。

Answer 2

提示されている仕様の場合、完全に同一な行だけ除外すれば良いので、DISTINCT を追加しましょう。

SELECT DISTINCT
  animal.id, staff_no, Jaopanese, English
  FROM staff
  LEFT JOIN animal
  ON staff.animal_id = animal.id ;

MySQL select文仕様

Comments

[退会済みユーザー]

ありがとうございます。DISTINCTを追加しても、表示結果は変わりませんでした。一番左側idのカラムが同一でないため、ueno_004は二回表示されてしまいます。もし、一番左のカラムにidを表示しなければ、DISTINCTは有効なのですが、左側にidは残したいため、つまづいています。

[YT0014]

ご提示いただいたSQLとその結果ですが、正しい組み合わせではないのではないでしょうか？
SQLの最初のカラムは、animal.idなので、結果は、上から、2,1,2,3,3となると思うのですが。

Answer 3

staffはスタッフさんのマスタテーブルかと思いきや
animalの担当テーブルみたいにも見えるのがちょっとまずい点
「ueno_004」さんは…性別がFとM両方ある…オカマかな？
animalの性別だとしたらanimalにsex列を設けるべきだし多分staffの性別なんでしょう。
animalの雄と雌で担当を区別する目的かとも思ったんですがanimalにageがあるので…（思考停止）

とりあえずSQL

SELECT min(animal.id) as id, staff_no, Jananese , English
  FROM staff
  LEFT JOIN animal
  ON staff.animal_id = animal.id ;
GROUP BY staff_no, Jananese , English

まあこうすれば求める結果にはなります。ただidの5を捨てる基準がわからない。
staffのid=5の列のanimal_idが3以外だったらどうするつもりなんでしょう。

Comments

[退会済みユーザー]

私のテーブル設計に問題がありました・・すみません。
「ueno_004」のsexをFに統一しました。（オカマではありません・・）
年齢や性別の設定はあまり意味はなく、LEFT JOINを使って、id | staff_no | Jananese | English 　のカラムを結合するという意図飲みで作ったテーブルでした。そのため、設定としてよくないテーブルになってしまいました。すみません。

idの5を捨てる基準ですが、一番左のカラムのid以外が同一だった場合、重複を避けたいという意図があります。例えば、

| 4 | ueno_004 | 猫 | cat |
| 5 | ueno_004 | 猫 | cat |

の場合は、　ueno_004 → 猫　が表示されれば良いので、id4と重複するid5の列は表示したくないのです。

[sousuke]

であれば多分提示のSQLでいいです

[退会済みユーザー]

ありがとうございます！！提示していただいたようにやったらできました！