odp.1
CREATE TABLE menu ( id int PRIMARY KEY, parent_id int, name varchar(255))
odp.2
ALTER TABLE menu ADD CONSTRAINT parent_id_fk FOREIGN KEY (parent_id) REFERENCES menu(id)
odp.3
trzecie pytanie jest bez sensu, gdzie masz wstawić te dane jak masz tylko 3 kolumny id, parent_id, name ? z tego co napisałeś mi wynika że trzeba by było tabele jeszcze jedną napisać żeby można było poniższą strukturę danych uzyskać i zrobić między nimi relacje ale o tym nic nie ma w pytaniu. Albo po prostu nie wiem o co kaman ;D
odp.4
pewnie będzie wyglądała jakoś tak:
;WITH Emp_CTE AS
(
SELECT id, parent_id, name, main, category1, category2
FROM #menu
WHERE parent_id IS NULL
UNION ALL
SELECT e.id, e.parent_id, e.name, e.main, e.category1, e.category2
FROM #manu e
INNER JOIN Emp_CTE ecte ON ecte.id = e.parent_id
)
SELECT * FROM Emp_CTE
DROP TABLE #menu
Wyrażenie :WITH robi następującą rzecz. Na początku wykonuje się SELECT który wybiera rekord o najwyższym szczeblu.
Na tym etapie zmienna CTE Emp_CTE ma jeden rekord.
Później są wykonywane następne SELECT-y które dodają następne rekordy do Emp_CTE tylko jeżeli wystąpiło JOIN-owanie pomiędzy wyższym rekordem a niższym
W ten sposób w kroku drugim CTE Emp_CTE ma 3 rekordy.
Postępując dalej w taki sam sposób będziemy mieli listę zgodną z poziomem w hierarchii.
Na końcu zapytania tabela tymczasowa jest likwidowana.
Jak widać rekurencja z CTE jest banalnie prosta ponieważ możemy odwołać się do jej samie wewnątrz zapytania które je tworzy.
ale sprawdź sobie jeszcze sam wszystko dokładnie bo to na szybko pisane pozdrawiam.