データベース副問い合わせについて、質問したいですが...

Question

昨日も同じ質問させていただきましたが、質問がちょっと違いましたので、改めて質問させていただきます。

状況を説明します。

table1

id	owner	staff
1	2	2
2	1	3
3	3	1
4	2	3
5	1	3

table2

id	user_id	name
1	abcd	山田
2	bcad	中村
3	cbda	高橋

データベースに二つのテーブルがありまして、
table1のownerとstaffの値はtable2のidです。
そしてownerとstaffのデータを取り出すときに、数字ではなく、
talbe2のidを見て、nameの値を出したいです。

そして、今のtable2にはid三つしかないですが、例えばtalbe1のownerとstaffに4もしくは5が存在するときには、table2のidを見て、ないidは[データありません]と表示したいです。

以上です。
説明下手で申し訳ないです。
まったくdbの初心者ですので、わかりやすくお願いいたします。
何卒、宜しくお願いいたします。

Answer 1

副問合せをするより、LEFT JOINを活用したほうがスッキリ書けると思います。

SELECT
 table1.id AS id,
 COALESCE(owners.name, '[データありません]') AS owner_name,
 COALESCE(staffs.name, '[データありません]') AS staff_name
FROM table1 
  LEFT JOIN table2 AS owners ON owners.id = table1.owner
  LEFT JOIN table2 AS staffs ON staffs.id = table1.staff

なお、「他のテーブルにない値を入れる必要がある」などの特段の事情がない限り、リレーションに使う列には外部キー制約を入れて、「参照先が見つからない」事態を避けるようにするべきだとは思います。

Comments

[masaya_ohashi]

COALESCEの存在を忘れてましたﾃﾍ

Answer 2

命題についてはこう

元データ

create table table1 (id int unique,owner int,staff int);
insert into table1 values(1,2,2),(2,1,3),(3,3,1),(4,2,3),(5,1,3),(6,4,1),(7,2,5),(8,4,5);
create table table2 (id int unique,user_id varchar(20),name varchar(20));
insert into table2 values(1,'abcd','山田'),(2,'bcad','中村'),(3,'cbda','高橋');

検索

select t1.id,coalesce(t2.name,'no one') as owner_name,coalesce(t3.name,'no one') as staff_name
from table1 as t1
left join table2 as t2 on t1.owner=t2.id
left join table2 as t3 on t1.staff=t3.id

ただし、問題点がいくつかあります。

• table2にないidをtable1に登録できる事自体がおかしい

この手のものは外部キー結合で処理する前提でかならず対応するものを用意しておくべき

• もし対応するものがない場合は、「データがありません」と表示するのではなく

そのキーに基づくデータ自体を表示させないようにすべき

• table1でownerやstaffを数値（table2のid）をキーにリレーションしているが、

ユーザーのヒモ付をするならuser_idを利用すべき

Answer 3

SELECT
  table1.id AS `id`,
  COALESCE(v_owner.name, 'ありません') AS `owner`,
  COALESCE(v_staff.name, 'ありません') AS `staff`
FROM
  table1
  LEFT OUTER JOIN table2 ON (table2.id = table1.owner) `v_owner`
  LEFT OUTER JOIN table2 ON (table2.id = table1.staff) `v_staff`
;

LEFT OUTER JOIN は、結合条件の左辺側が「存在しないとき」に、代わりに「すべてのカラムが null」というダミー列が結合されます。
そして COALESCE は、引数の中で左から順に見て、「最初に見つけた null ではない値」を返します。
これを組み合わせることで、owner や staff が table2 でいない場合に（v_owner.name や v_staff.name が null になる）、「ありません」という結果になるようになっています。

既に他の方も書かれていますが、データの整合性を担保する意味からも、整合性制約（foreign key）を設定して、そもそも ありません という状態にならないようにするのが定法です。

Answer 4

SELECT
  t1.id,
  IF(t2o.name IS NULL, "データがありません", t2o.name) AS ownerName,
  IF(t2s.name IS NULL, "データがありません", t2s.name) AS staffName
FROM
  table1 AS t1
  LEFT JOIN table2 AS t2o ON t1.owner = t2o.id
  LEFT JOIN table2 AS t2s ON t1.staff = t2s.id
;

こんなかんじですかね。

• ownerとtable2をひも付けてt2oとする(LEFT JOIN 1行目)
• staffとtable2をひも付けてt2sとする(LEFT JOIN 2行目)
• t2oからnameを取ってownerNameとする、ない場合はデータがありませんとする(IF 1行目)
• t2sからnameを取ってstaffNameとする、ない場合はデータがありませんとする(IF 2行目)

補足

ASというのは「◯◯は◯◯という仮名を付ける」という使い方で、table1をいちいち書くのが面倒だからt1としたり、こういう条件でひも付けた方はこういう名前、こっちの条件でひも付けたほうはこういう名前、と区別を付けるために使うAlias(エイリアス、別名)をつけるものです。