SQLでテーブルを結合して表示

Question

前提・実現したいこと

以下の２つようなテーブル(table1, table2)があります。
これらを結合し、一つのテーブル(table3)として表示したいのですが、結合が上手くいきません。

結合の仕方がおかしいのは分かるのですが、そもそも今回のような結合方法が可能なのかもわかりません。
SQLに関して全くの初心者ですが、ご指導いただけますと幸いです。
よろしくお願いします。

table1↓

user_id	f_name	l_name
001	taro	yamada
002	hanako	tanaka

table2↓

user_id	key	value
001	area	tokyo
001	email	[e-mail]
002	area	osaka
002	email	[e-mail]

table3↓

user_id	f_name	l_name	area	email
001	taro	yamada	tokyo	[e-mail]
002	hanako	tanaka	osaka	[e-mail]

該当のソースコード

$query = "SELECT * FROM table1 LEFT OUTER JOIN table2";

$results = $db->get_results($query);
foreach ($results as $row) {
  echo
    "<tr>\n",
    " <td>" . $row->user_id . "</td>\n",
    " <td>" . $row->f_name . "</td>\n",
    " <td>" . $row->l_name . "</td>\n",
    " <td>" . $row->area . "</td>\n",
    " <td>" . $row->email . "</td>\n",
    "</tr>\n";
}

Comments

[Orlofsky]

テーブル設計の基本に1つの列には1種類のデータしか設定してはならない、という掟があります。これを守らないとどんどんSQLが複雑になってパフォーマンスも悪くなります。

Answer 1

たとえば

select user_id,
group_concat(case `key` when 'area' then value else null end) as area,
group_concat(case `key` when 'email' then value else null end) as email
from tbl2
group by user_id

Comments

[yambejp]

これをサブクエリにしてtable1にjoinしてください

[sazi]

単なるEAVだから集計は不要じゃないでしょうか。

[yambejp]

group_concatをつかっているのはいわゆるピボット処理です。
table2のkey=valueの組み合わせがおそらく増えていく前提のデータの
持ち方なので、何度もjoinするより形にしてから１度だけjoinするほうが
書きやすいかなと言う提案です（効率的かといわれる怪しいですが）

[sazi]

table2には一意キーが無い（若しくは全てで一意）前提という事ですね。

[yambejp]

今回の件、勝手に「table2のuser_id、keyの組み合わせはユニーク」と判断しましたが
仕様次第でしょうね

[sazi]

ああ、趣旨はクロス集計しておくって事ですね。
group_concat()に惑わされていました。

[yambejp]

わかりにくくてすみません
数値のピボットはsumでできるので直感的ですけど、文字列のピボットは
group_concatしないといけないのでちょっと戸惑いますよね

[sousuke]

keyが一意ならmaxでもよくない？MySQL触ったことないからわからんのだけどgroup_concatって重そう…
max(case `key` when 'area' then value end) as area

Answer 2

同じテーブルでも、別名を付けると、何度でも結合は可能です。

select t1.*, area.value, email.value
from  table1 t1
      left join table2 area
      on    t1.user_id = area.user_id 
        and area.key='area'
      left join table2 email
      on    t1.user_id = email.user_id 
        and email.key='email'