Postgresqlの自己結合について

first_table
f_idf_name
1鈴木
2田中
3佐藤

last_table
l_idl_name
1一郎
1二郎
2太郎
3次郎

欲しい結果
idf_namel_name1l_name2
1鈴木一郎二朗
2田中太郎（空白もしくはNULL）
3佐藤次郎（空白もしくはNULL）

PHP4.3.2とPostgresql7.4.29の環境です。
first_tableとlast_tableの2つのテーブルがあって、
欲しい結果のような表を作成したいです。
l_name1やl_name2でのキーワード検索も行いたいので、1つのSQL文を発行したいのですが、
どのように書けば良いのでしょうか？

last_tableを自己結合し、first_tableと結合するのが近い気がするのですが。
SELECT * FROM first_table AS f
INNER JOIN (
SELECT l1.l_id, l1.l_name AS l_name1, l2.l_name AS l_name2
FROM l_table AS l1, l_table AS l2
WHERE l1.l_name > l2.l_name
AND l1.l_id = l2.l_id
) AS l
ON f.f_id=l.l_id

よろしくお願いします。

No.3 回答者： Masaru_Sugawara 回答日時： 2011/10/30 16:28

名前のテーブルは繰り返して結合すことになるので、一般的には無理です。

ただし、名前のテープルがせいぜい10種類くらいであれば、
outer joinで10個程度をつなげることでは可能です。

新しいバージョンであれば、with recursive では実現可能です。

No.2 回答者： ky072 回答日時： 2011/10/19 22:06

VIEW を使ってみてはいかがですか。

CREATE TEMPORARY VIEW tmpview AS 《さきほどのSQL》;

SELECT * FROM tmpview WHERE l_name1='○○';

PostgreSQL のバージョンが古いので、
TEMPORARY VIEW が使えないかもしれません。
その場合には「TEMPORARY」を削除すれば
VIEW が永続的に残りますが動くと思います。

この回答へのお礼

回答ありがとうございます。
以下のようなSQLだとエラーになってしまいます。

CREATE TEMPORARY VIEW tmpview AS
SELECT F.f_id,F.f_name,
(SELECT L.l_name FROM last_table L
WHERE L.l_id=F.f_id ORDER BY L.l_name
LIMIT 1 OFFSET 0) AS l_name1,
(SELECT L.l_name FROM last_table L
WHERE L.l_id=F.f_id ORDER BY L.l_name
LIMIT 1 OFFSET 1) AS l_name2
FROM first_table F;

お礼日時：2011/10/20 01:20

No.1 回答者： ky072 回答日時： 2011/10/19 18:23

こんな感じでいかがでしょう。

SELECT F.f_id,F.f_name,
(SELECT L.l_name FROM last_table L
WHERE L.l_id=F.f_id ORDER BY L.l_name
LIMIT 1 OFFSET 0) AS l_name1,
(SELECT L.l_name FROM last_table L
WHERE L.l_id=F.f_id ORDER BY L.l_name
LIMIT 1 OFFSET 1) AS l_name2
FROM first_table F;

この回答へのお礼

回答ありがとうございます。
表示は希望通りで完璧なのですが、
l_name1やl_name2で検索をしようとするとエラーになってしまいます。
検索まで出来ればいうことないのですが。
（f_idやf_nameでの検索は出来ます。）

例えば、
上記のSQL文に
WHERE l_name1 = '一郎'
を付け加えて検索しようとすると、「column "l_name1" does not exist」というエラー

上記のSQL文に
WHERE l_name = '一郎'
を付け加えて検索しようとすると、「column "l_name" does not exist」というエラー

上記のSQL文に
WHERE L.l_name1 = '一郎'
を付け加えて検索しようとすると、「relation "l" does not exist」というエラー

上記のSQL文に
WHERE L.l_name = '一郎'
を付け加えて検索しようとすると、「relation "l" does not exist」というエラー

となってしまいます。
こちらの解決方法も分かれば教えて下さい。
よろしくお願いします。

お礼日時：2011/10/19 21:40