SQL文で、複数のテーブルから複数の条件に合うデータをSELECTしたい

Question

オンライン実行環境

下記にて「今のコード」を記入済みのため、動作を試せます。
http://sqlfiddle.com/#!17/a6df3c3/1

現状とやりたいこと

「今のコード」のSELECT文の実行結果は、下記表のようになります。

time	id	id	id	name	name	name
2020-08-11	1	1	1	hoge	piyo	fuga
2020-08-11	1	2	1	hoge	piyopiyo	fuga
2020-08-11	2	1	1	hogehoge	piyo	fuga
2020-08-11	2	2	1	hogehoge	piyopiyo	fuga
2020-08-11	1	1	2	hoge	piyo	fugafuga
2020-08-11	1	2	2	hoge	piyopiyo	fugafuga
2020-08-11	2	1	2	hogehoge	piyo	fugafuga
2020-08-11	2	2	2	hogehoge	piyopiyo	fugafuga

しかし、今得たいと思っているのは、下記です。

time	id	id	id	name	name	name
2020-08-11	1	1	1	hoge	piyo	fuga
2020-08-11	2	2	2	hogehoge	piyopiyo	fugafuga

つまり、時間とidが同じもののみを結合させて取得したいのですが、
どのようにしたらよいでしょうか。

今のコード

create table meter1 (
  observed DATE, 
  id integer,
  name varchar(10)
);
create table meter2 (
  observed DATE, 
  id integer,
  name varchar(10)
);

create table camera (
  recorded DATE,
  id integer,
  name varchar(10)
);

insert into meter1 values('2020-08-11','1', 'hoge');
insert into meter1 values('2020-08-11','2', 'hogehoge');
insert into meter2 values('2020-08-11','1', 'piyo');
insert into meter2 values('2020-08-11','2', 'piyopiyo');
insert into camera values('2020-08-11','1', 'fuga');
insert into camera values('2020-08-11','2', 'fugafuga');

SELECT
  case
    when meter1.observed is not null then meter1.observed
    when meter2.observed is not null then meter2.observed
    when camera.recorded is not null then camera.recorded
  end as "time",
  meter1.id, meter2.id, camera.id,
  meter1.name, meter2.name, camera.name
FROM
  meter1
  FULL OUTER JOIN meter2 ON (meter1.observed = meter2.observed)
  FULL OUTER JOIN camera ON (meter1.observed = camera.recorded)

Comments

[Orlofsky]

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先の説明](http...) に修正してください。

[yamekodev]

ご指摘ありがとうございます。修正しました。

Answer 1

idを集約するのに、３つ以上のテーブルでfull outerで行うには無理があります。
一旦全てのIDのリストを取得し、それを元に結合します。

SELECT base.id, mybook.name, mybook2.name, mybook3.name, mybook4.name
FROM (
            select id from mybook
      union select id from mybook2
      union select id from mybook3
      union select id from mybook4
  ) base
  left join mybook  ON base.id = mybook.id
  left join mybook2 ON base.id = mybook2.id
  left join mybook3 ON base.id = mybook3.id
  left join mybook4 ON base.id = mybook4.id

以下は冗長になりますが、unionマージでない分高速であるかもしれません。
特にidに対してindexが設定されているようならさらに。

SELECT base.id, mybook.name, mybook2.name, mybook3.name, mybook4.name
FROM (
      select id from (
                  select id from mybook
        union all select id from mybook2
        union all select id from mybook3
        union all select id from mybook4
      ) step1
      group by id
  ) base
  left join mybook  ON base.id = mybook.id
  left join mybook2 ON base.id = mybook2.id
  left join mybook3 ON base.id = mybook3.id
  left join mybook4 ON base.id = mybook4.id

質問が変更されたので追記

SELECT base.id, meter1.observed, meter2.observed, camera.recorded
FROM (
      select observed, id from (
                  select observed, id from meter1
        union all select observed, id from meter2
        union all select recorded, id from camera
      ) step1
      group by observed, id
  ) base
  left join meter1 ON base.observed= meter1.observed and base.id = meter1.id
  left join meter2 ON base.observed= meter2.observed and base.id = meter2.id
  left join camera ON base.observed= camera.recorded and base.id = camera.id

Comments

[yamekodev]

こちらの上のやり方で解決しました。
解決内容は後程追記します。
本当にありがとうございました。
ベストアンサーをつけなおせるか今運営に問い合わせ中です。

[sazi]

解決積みは外せると思います。

[yamekodev]

遅くなり申し訳ございません。
ベストアンサーをこちらの回答に付け替えさせていただきました。
今回はいろいろとありがとうございました。大変助かりました。

Answer 2

sazi様の方法を応用して、idでフィルタした結果に対して、where句で時間を指定することで実現できました。
本当にありがとうございました。
http://sqlfiddle.com/#!17/c64c83/14

select
  base2.id,
  base2.observed1,
  base2.observed2,
  base2.recorded
from (
  select
    base1.id,
    meter1.observed AS observed1,
    meter2.observed AS observed2,
    camera.recorded,
    meter1.name,
    meter2.name,
    camera.name
  from (      select id from meter1
        union select id from meter2
        union select id from camera
    ) base1
    left join meter1 ON base1.id = meter1.id
    left join meter2 ON base1.id = meter2.id
    left join camera ON base1.id = camera.id
  ) base2
where (base2.observed1 = base2.observed2)
  AND (base2.observed2 = base2.recorded) 
  AND (base2.recorded = base2.observed1) 

以下は自己解決したと勘違いしていたときの内容です。
念のため消さずに残しておきます

---

要素の名前が違うことをサンプルに入れておらず、正しい質問ができていませんでした。
申し訳ありませんでした。
USINGを使って実現できました。

create table mybook (
  observed integer, 
  name varchar(10)
);
create table mybook2 (
  observed integer, 
  name varchar(10)
);

create table mybook3 (
  recorded integer, 
  name varchar(10)
);

insert into mybook values('1', 'hoge');
insert into mybook2 values('1', 'hogepiyo');
insert into mybook2 values('2', 'hogehoge');
insert into mybook3 values('2', 'piyo');

SELECT
  case
    when mybook.observed is not null then mybook.observed
    when mybook2.observed is not null then mybook2.observed
    when mybook3.recorded is not null then mybook3.recorded
  end as "time",
  mybook.name,
  mybook2.name,
  mybook3.name
FROM
  mybook
  FULL OUTER JOIN mybook2 USING (observed)
  FULL OUTER JOIN mybook3 ON (observed = mybook3.recorded)

Comments

[sazi]

using は同じ項目名の場合に記述を省略できるだけです。
上手くいったというのはテーブル数が減っているからで、質問の前提が変わっただけだと思いますけど。
試しにテーブル数を質問と同様にすると、何も変わっていない事が分かるはずです。

[yamekodev]

すみませんご指摘の通りでした・・・
改めて、本来やりたいことと近いテーブルを作ったので、質問を編集します。

[sazi]

解決された記述は少し冗長なので、追記しておきました。

[yamekodev]

なるほど、と目からうろこの素晴らしい追記内容でした。
ググっても基本的な内容しかほぼ見つからず困っていましたが、
sazi様のようなきれいな方法を見るととても勉強になります。
何度もありがとうございます。

[sazi]

こちらの解決方法だとwhere条件にNull考慮（結合されない場合）がないので、正しく抽出されなかったりもします。

Answer 3

下記でもいいですね。

SELECT base.id, max(n1) name1, max(n2) name2, max(n3) name3, max(n4) name4
FROM (
      select id, name n1, null n2, null n3 , null n4 from mybook
      union all select id, null, name, null, null from mybook2
      union all select id, null, null, name, null from mybook3
      union all select id, null, null, null, name from mybook4
  ) base
  group by id;