【MySQL】[同名カラムをひとまとめにしたい]

Question

user

+----------+------------+
| user_id  |   location |
+----------+------------+
|      001 |      tokyo |
|      002 |      tokyo |
|      003 |      osaka |
|      004 |      tokyo |
|      005 |      osaka |
+----------+------------+

user_stay001

+----------+--------+
| user_id  |   stay |
+----------+--------+
|      001 |      2 |
|      002 |      6 |
|      003 |      4 |
+----------+--------+

user_stay002

+----------+--------+
| user_id  |   stay |
+----------+--------+
|      004 |      1 |
|      005 |      8 |
+----------+--------+

[理想の結果]

+----------+--------+------------+
| user_id  |   stay |   location |
+----------+--------+------------+
|      001 |      2 |      tokyo |
|      002 |      6 |      tokyo |
|      003 |      4 |      osaka |
|      004 |      1 |      tokyo |
|      005 |      8 |      osaka |
+----------+--------+------------+

・INNER JOINやUNIONなどするとカラム名が被ってエラーになる

どのようなSQLを書けばよいでしょうか？
宜しくお願い致します。

SELECT
 u.user_id,
 u.location
 stay
   -- user_stay001、user_stay002のテーブルからstayを持ってきてひとまとめにする
FROM
 user u
<????>

Comments

[sonor_3rd]

情報の追加をお願いします。
user_stayテーブルのuser_idはuniqueでしょうか？
user_stay001とuser_stay002で、同じuser_idが入ることはありますか？

[star24star]

uniqueです！同じuser_idは別テーブルに存在しません

Answer 1

SELECT
    U.user_id,
    COALESCE(U1.stay,U2.stay,'') AS stay,
    U.location
FROM user U
LEFT JOIN user_stay001 U1 ON U1.user_id = U.user_id
LEFT JOIN user_stay002 U2 ON U2.user_id = U.user_id

こんな感じでしょうか。

Comments

[star24star]

ご回答有難うございます！
---
COALESCE(value,...)
---
引数の(各テーブルからstayの)データを取得してきて(なければnull)くっつけていくといったイメージでしょうか？
ちなみに最後の ' ' の意味を教えていただけるとありがたいです。

[dupont_kedama]

COALESCE()は左から順番に評価し、最初にNULL以外が見つかったらそれを返す関数です。
（たとえば004の場合はU1.stayがNULLになり、U2.stayに'1'が入ります。）

最後に''を入れている理由は、user_stay001にもuser_stay002にもuser_idが見つからなかった場合にNULLではなく空文字を返すようにします。
アプリケーション側でNULLを扱わせないための工夫であり保険ですので必須ではありません。
（この構造だと「どちらかのテーブルに確実に存在する」ことをDBMSでは制約できないため、私の場合はこうします。）

[star24star]

>> たとえば004の場合はU1.stayがNULLになり、U2.stayに'1'が入ります。
先に理想結果のようなU1とU2が合わさったテーブルが作られ、U2に１が存在するから１のデータが入るのでしょうか？

[dupont_kedama]

そうですね。以下を試してみてください。

SELECT U.user_id, U1.stay AS U1stay, U2.stay AS U2stay, U.location
FROM user U
LEFT JOIN user_stay001 U1 ON U1.user_id = U.user_id
LEFT JOIN user_stay002 U2 ON U2.user_id = U.user_id

2列目→3列目の順番に評価して、NULL以外があったらそれを返すことで、「ひとまとめ」にするという方法です。

[star24star]

なるほど…！有難うございます！！