UNIONとJOINを組み合わせて並べ替えをするSQL文について

Question

atableとbtableをソートのために連結させて運用してましたが、ctableを結合させる必要が出来たためUNIONで対応しようとしています。

追記：データベースエンジンはMYSQLです。
ccodeがあるのはbtableのみで、データ並びはSELECTしているacolとbcolのみで、ccodeでソートしたものが必要です。

追記2：MYSQLのエラーです。コードは以下です。
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ccode FROM `atable` LEFT JOIN `btable` ON `atable`.`acode`=`btable`.`acode` at line 1

btableとatable(ctable)と一致しているのはacodeのみです。

atable
acode
bcode
acol
bcol

btable
acode
ccode
ccol
dcol

ctable
acode
bcode
acol
bcol


$sql="SELECT `atable`.`acol` AS acol,`atable`.`bcol` AS bcol FROM `atable`";
$sql.=" LEFT JOIN `btable` ON `atable`.`acode`=`btable`.`acode`";
$sql.=" WHERE `atable`.`bcode` = ".$bcode;
$sql.=" ORDER BY `ccode";

現在のSQL文です。


$sql="SELECT `atable`.`acol` AS acol,`atable`.`bcol` AS bcol FROM `atable`";
$sql.=" LEFT JOIN `btable` ON `atable`.`acode`=`btable`.`acode`";
$sql.=" WHERE `atable`.`bcode` = ".$bcode;
$sql.=" UNION SELECT `ctable`.`acol` AS acol,`ctable`.`bcol` AS bcol FROM `ctable`";
$sql.=" LEFT JOIN `btable` ON `ctable`.`acode`=`btable`.`acode`";
$sql.=" WHERE `atable`.`bcode` = ".$bcode;
$sql.=" ORDER BY `ccode";

以上ではソートできず、項目別名が必要という情報があったので以下のようにしましたが構文エラーになってしまいます。


$sql="SELECT `atable`.`acol` AS acol,`atable`.`bcol` AS bcol,`ctable`.`ccode` AS order FROM `atable`";
$sql.=" LEFT JOIN `btable` ON `atable`.`acode`=`btable`.`acode`";
$sql.=" WHERE `atable`.`bcode` = ".$bcode;
$sql.=" UNION SELECT `ctable`.`acol` AS acol,`ctable`.`bcol` AS bcol ,`ctable`.`ccode` AS order FROM `ctable`";
$sql.=" LEFT JOIN `btable` ON `ctable`.`acode`=`btable`.`acode`";
$sql.=" WHERE `atable`.`bcode` = ".$bcode;
$sql.=" ORDER BY `order";

色々調べましたがどのようなSQL文を書けばよいか分かりません。
どうぞご教授お願いいたします。

Accepted Answer

atableとctableをunionしてるってことはfull outer joinを行いたいという事で解釈すると、以下のようになるかと。
```SQL
select AC.acol, AC.bcol
from (
      SELECT acode, acol, bcol FROM atable WHERE bcode = $bcode
      union 
      SELECT acode, acol, bcol FROM ctable WHERE bcode = $bcode
    ) as AC 
    LEFT JOIN btable as B ON AC.acode=B.acode
ORDER BY B.ccode
```
他の方とおなじでしたね。。。

Answer

テーブルの定義はCREATE TABLEで提示された方が適切なコメントが付き易いです。
[UNION、UNION ALL 集合演算子](https://www.shift-the-oracle.com/sql/union-operator.html) の違いは理解した上で使ってください。

SQLの部分だけ、個人的にわかり易い記述で
```SQL
SELECT D.acol
     , D.bcol
FROM(
    SELECT A.acol
         , A.bcol
         , B.ccode
    FROM atable A
    LEFT JOIN btable B
    ON A.acode = B.acode
    WHERE A.bcode = $bcode
    UNION ALL
    SELECT C.acol
         , C.bcol
         , B.ccode
    FROM ctable C
    LEFT JOIN btable B
    ON C.acode = B.acode
    WHERE C.bcode = $bcode
    ) D
ORDER BY D.ccode;
```

Answer

atableとctableを結合した後にbtableをleft joinしてはどうでしょうか？

```php
$sql="SELECT anc.acol,anc.bcol";
$sql=" FROM (SELECT acode,acol,bcol FROM atable";
$sql=" UNION SELECT acode,acol,bcol FROM ctable) anc";
$sql=" LEFT JOIN btable ON anc.acode = btable.acode";
$sql=" WHERE anc.bcode = ".$bcode;
```

Answer

通るかどうかためしてないけど、直してみた。
[ヒアドキュメント構文](http://php.net/manual/ja/language.types.string.php#language.types.string.syntax.heredoc)を使えば見通しが良くなるよ、と。

```
$sql=<<<EOT
SELECT X.acol, X.bcol, X.order
FROM (
    SELECT atable.acol AS `acol`, atable.bcol AS `bcol`, ctable.ccode AS `order` 
    FROM atable
    LEFT JOIN btable ON atable.acode = btable.acode
    WHERE atable.bcode = {$bcode}
    UNION 
    SELECT ctable.acol AS `acol`, ctable.bcol AS `bcol`, ctable.ccode AS `order` 
    FROM ctable
    LEFT JOIN btable ON ctable.acode = btable.acode
    WHERE atable.bcode = {$bcode}
) AS X
ORDER BY X.`order`
EOT;
```

追記を観たので、atableとctable先にunionしたものにbtableをleft joinした方がいいんじゃないかと、
書き直してみた。
```
$sql = <<<EOT
SELECT X.acol, X.bcol, btable.ccode
FROM (
    SELECT atable.acode, atable.acol, atable.bcol
    FROM atable
    WHERE atable.bcode = {$bcode}
    UNION
    SELECT ctable.acode, ctable.acol, ctable.bcol
    FROM ctable
    WHERE ctable.bcode = {$bcode}
) AS X
LEFT JOIN btable ON X.acode = btable.acode
ORDER BY btable.ccode
EOT;
```

関連した質問