SQL join to grab data from same table via intermediate table

Question

Hi

Could someone help me with building the following query. I have a table called Sites, and one called Site_H. The two are joined by a foreign key relationship on page_id. So the Sites table contains pages, and the Site_H table shows which pages any given page is a child of by having another foreign key relation back to the site table with a column called ParentOf.

So, a page can be have another page as a parent. Other data is stored in the Site_H table such as position etc, hence why it is separated out.

I would like a query that returns the details of a page along with the details of its parent page. I just cant quite think about how to structure the SQL.

Thanks

Answer 1

SELECT  sc.*, sp.*
FROM    Sites sc
JOIN    Site_H h
ON      h.parentOf = sc.page_id
JOIN    Sites sp
ON      sp.page_id = h.page_id
WHERE   sc.page_id = @mypage