質問編集履歴
2
sqlの誤りを修正しました
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -56,24 +56,14 @@
|
|
|
56
56
|
```sql
|
|
57
57
|
select *
|
|
58
58
|
from main
|
|
59
|
-
|
|
59
|
+
where id = 31
|
|
60
|
-
|
|
60
|
+
or id = (select parent_id
|
|
61
|
-
|
|
61
|
+
from main
|
|
62
|
-
|
|
62
|
+
where id = (select parent_id
|
|
63
|
-
|
|
63
|
+
from main
|
|
64
64
|
where id = 31))
|
|
65
|
-
|
|
65
|
+
or id = (select parent_id
|
|
66
|
-
|
|
66
|
+
from main
|
|
67
|
-
where id = 31)select *
|
|
68
|
-
from main
|
|
69
|
-
where id = 31
|
|
70
|
-
or id = (select parent_id
|
|
71
|
-
from main
|
|
72
|
-
where id = (select parent_id
|
|
73
|
-
from main
|
|
74
|
-
where id = 31))
|
|
75
|
-
or id = (select parent_id
|
|
76
|
-
from main
|
|
77
|
-
|
|
67
|
+
where id = 31)
|
|
78
68
|
```
|
|
79
69
|
|
1
ベストアンサーをもとに実装方法を追加
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -48,3 +48,32 @@
|
|
|
48
48
|
JOIN `main` t2 ON t1.id = t2.parent_id
|
|
49
49
|
WHERE t1.id = 3;
|
|
50
50
|
```
|
|
51
|
+
|
|
52
|
+
### まとめ（メモ）
|
|
53
|
+
教えて頂いた方法を使って、下記のようにして、親、子供、孫 の3レコード取得が出来ました。
|
|
54
|
+
※実際のテーブルには最大5階層あるため、下記のようなsqlを階層の分、PHPで生成する形になりそうです。
|
|
55
|
+
|
|
56
|
+
```sql
|
|
57
|
+
select *
|
|
58
|
+
from main
|
|
59
|
+
where id = 31
|
|
60
|
+
or id = (select parent_id
|
|
61
|
+
from main
|
|
62
|
+
where id = (select parent_id
|
|
63
|
+
from main
|
|
64
|
+
where id = 31))
|
|
65
|
+
or id = (select parent_id
|
|
66
|
+
from main
|
|
67
|
+
where id = 31)select *
|
|
68
|
+
from main
|
|
69
|
+
where id = 31
|
|
70
|
+
or id = (select parent_id
|
|
71
|
+
from main
|
|
72
|
+
where id = (select parent_id
|
|
73
|
+
from main
|
|
74
|
+
where id = 31))
|
|
75
|
+
or id = (select parent_id
|
|
76
|
+
from main
|
|
77
|
+
where id = 31)
|
|
78
|
+
```
|
|
79
|
+
|