SQL join to capture data from one table through a staging table

Question

Can someone help me create the following query. I have a table called "Sites" and one called Site_H. These two connections are associated with a foreign key on id_page. Thus, the Sites table contains pages, and the Site_H table shows which pages each given page is a child of, taking into account the relationship of the foreign key to the site table with the ParentOf column.

Thus, the page may have another page as a parent. Other data is stored in the Site_H table, such as position, etc., therefore it is shared.

I need a query that returns page information along with the details of its parent page. I just can't think about how to structure SQL.

thank

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