縦結合と変数設定のクエリ

ユニオン結合で困っています。
以下のような3つのテーブルがあり、正規化したいため以下のようなユニオン結合と変数を設定したクエリを作成したいです。

加工前のテーブルはtbl1,tbl2,tbl3とあり各フィールド名は以下のとおりです。
tbl1: ID,右眼術前検査日,左眼術前検査日
tbl2: ID,右眼手術検査日,左眼手術検査日
tbl3: ID,右眼術後検査日,左眼術後検査日

これらのテーブルを以下のようにするクエリはどのようにしたらよいでしょうか？

求めるクエリqry
qry: ID, 眼, 検査時期, 検査日
　　 101,右, 術前, 2007/01/01
101,右, 手術, 2007/01/05
101,右, 術後, 2007/01/06
101,左, 術前, 2007/01/02
101,左, 手術, 2007/01/05
....

テーブルによって変数を設定するところの組み合わせがよく分からなくて困っています。大変恐縮ですが、ご教示のほどよろしくお願いします。

No.3 回答者： noname#140971 回答日時： 2007/09/07 16:09

少し、雑な回答に反省・・・。

補足：<tab4>を作成するコードは僅か6行。

[イミディエイト]
? CNNExecute("INSERT INTO tab4 SELECT 患者_ID,0 As 期間区分,0 As 左右,左目術前検査日 As 検査日 FROM tab1")
True
? CNNExecute("INSERT INTO tab4 SELECT 患者_ID,0 As 期間区分,1 As 左右,右目術前検査日 As 検査日 FROM tab1")
True
? CNNExecute("INSERT INTO tab4 SELECT 患者_ID,1 As 期間区分,0 As 左右,左目手術検査日 As 検査日 FROM tab2")
True
? CNNExecute("INSERT INTO tab4 SELECT 患者_ID,1 As 期間区分,1 As 左右,右目手術検査日 As 検査日 FROM tab2")
True
? CNNExecute("INSERT INTO tab4 SELECT 患者_ID,2 As 期間区分,0 As 左右,左目術後検査日 As 検査日 FROM tab3")
True
? CNNExecute("INSERT INTO tab4 SELECT 患者_ID,2 As 期間区分,1 As 左右,右目術後検査日 As 検査日 FROM tab3")
True

<tab4>
ID__患者_ID___期間区分___左右___検査日
12_______101________術前___左_______2007/08/11
13_______101________術前___右_______2007/08/11
14_______101________手術___左_______2007/08/12
15_______101________手術___右_______2007/08/12
16_______101________術後___左_______2007/08/15
17_______101________術後___右_______2007/08/15

なお、CNNExecute関数は、自作のSQL文の実行関数です。

ところで、UNION クエリに挑んでみました。（生まれて初めてでシンド！）

<クエリ1>
SELECT 患者_ID,0 As 期間区分,0 As 左右,左目術前検査日 As 検査日 FROM tab1 WHERE 患者_ID=101
UNION SELECT 患者_ID,0 As 期間区分,1 As 左右,右目術前検査日 As 検査日 FROM tab1 WHERE 患者_ID=101;

患者_ID__期間区分__左右___検査日
_____101____________0______0____2007/08/11
_____101____________0______1____2007/08/11

Where節の条件を変えれば・・・

患者_ID__期間区分__左右___検査日
_____101____________0_____0_____2007/08/11
_____101____________0_____1_____2007/08/11
_____102____________0_____0_____2007/08/11
_____102____________0_____1_____2007/08/11

同じ要領で３つのテーブルを結合すればOKであることも確認しました。

0,1は、関数で所定の文字列に変換されたらいいです。

No.2 回答者： noname#140971 回答日時： 2007/09/07 13:38

服飾デザイナーでプログラマではないので難しいクエリはサッパリ。

そこで、複雑なクエリを回避する私なりの方法を・・・。

<tab4>
ID____________long
患者_ID_____long
期間区分___byte
左右_________byte
検査日______date

[実際にデータを登録してコピぺ]

ID___患者_ID____期間区分___左右___検査日
1__________101_________術前______左___2007/08/11
2__________101_________術前______右___2007/08/11
3__________101_________手術______左___2007/08/12
4__________101_________手術______右___2007/08/12
5__________101_________術後______左___2007/08/15
6__________101_________術後______右___2007/08/15

通常は、テーブルをこのように設計しますよね。
このテーブルにクエリで見た目だけでも戻そうということですよね。
ならば、そういう関数　CreateTempTab4() を作成。

SELECT CreateTempTab4('tab1', 'tab2', 'tab3');"

これでも、一応、クエリには違いないでしょう。
なお、CreateTempTab4（）のソースをなんて補足はなしです。
（そこは、武士の情けってもんでお願いします）

No.1 回答者： t-ka 回答日時： 2007/09/07 12:59

例えばこんな感じになります。

select *
from(
select id, "右" as 眼,"術前" as 検査時期,右眼術前検査日 as 検査日 from tbl1
union
select id, "左" as 眼,"術前" as 検査時期,左眼術前検査日 as 検査日 from tbl1
)
where id = 患者ID
;