親子関係のレコードを1度にまとめてSELECTしたい

Question

実現したいこと

idを元にSELECTしたレコードと、
そのレコードの parent_idを持つレコード
の2行を 1回のクエリで同時にSELECT する事は可能でしょうか。

(googleで何と検索すれば いいでしょうか。）

例:
下記のテーブルにて、id=3 を元にしたとすると、

まず、くだもの レコードがSELECTされます。
くだもの レコードの parent_id は 1 なので、
id = 1 である、食べ物 レコード をSELECTします。

この時、くだもの レコードと、食べ物レコード の 2行を1回のクエリで取得したいです。

id	parent_id	data
1	null	食べ物
2	null	乗り物
3	1	くだもの
4	1	お菓子

▽取得したい配列の形

[
	[
		'id'=>3
		'parent_id'=>1,
		'data'=>'くだもの'
	],
	[
		'id'=>1
		'parent_id'=>null,
		'data'=>'食べ物'
	],
]

試したこと

下記のコードでは、とある子供レコードの親レコードの取得はできますが、子供レコードが取得できなくて困っています。

SELECT t2.* 
FROM `main` t1
JOIN `main` t2 ON t1.id = t2.parent_id
WHERE t1.id = 3;

まとめ（メモ）

教えて頂いた方法を使って、下記のようにして、親、子供、孫 の3レコード取得が出来ました。
※実際のテーブルには最大5階層あるため、下記のようなsqlを階層の分、PHPで生成する形になりそうです。

select * 
	from main 
	where id = 31
	or id = (select parent_id
				from main
				where id = (select parent_id
							from main
							where id = 31))
	or id = (select parent_id
						 from main
						where id = 31)

Comments

[logres_Fan]

> WHERE t1.id = 3
WHERE t2.id = 3に変えると、何か変わりますか？

[ID_7UGzV8hCHGs5]

ありがとうございます。
提案して頂いた通り下記のようにい変更してみると、
```sql
SELECT t2.*
FROM `main` t1
JOIN `main` t2 ON t1.id = t2.parent_id
WHERE t2.id = 3;
```

id=3 である　くだものレコードが 1行SELECTされました。

Answer 1

select * 
  from main 
 where id = 3
    or id = (select parent_id
               from main
              where id = 3);

Comments

[ID_7UGzV8hCHGs5]

教えて頂いたSQLでできました。ありがとうございます。

Answer 2

create table tbl(id int primary key,pid int null,data varchar(20));
insert into tbl values
(1,null,'食べ物'),
(2,null,'乗り物'),
(3,1,'くだもの'),
(4,1,'お菓子');

select * from tbl where 1 in(id,pid)

ただし今回のように親idを保持するような管理テーブルの場合、木構造で入れ子が複雑になってくるとテーブルの構造を変更する必要がでてきます
プロシージャなどで入れ子集合モデルでのテーブル拡張を検討ください

Comments

[ID_7UGzV8hCHGs5]

検証までしていただきありがとうございます。

>入れ子集合モデル
こちらについてちょっと調べてみました。非常に面白いアイディアですね
難しそうですが勉強してみますっ