CODEWITHSUNDEEP
sqlsql-server
Muhmmad Kuti
Muhmmad Kuti

Reputation: 105

Best way to select hierchial data from different SQL tables

I'm using SQL Server 2012. I have a hierarchical structure of tables for categories, pages, and sections.

Each category has zero or more pages and each page has zero or more sections. I would like to display all categories that match a certain query as well as all of their respective pages, in line with them, and then all of those pages' respective sections.

For example:

Category 1:
 Page A
 Page B

Category 2
 Page C (section 1, section 2, section 3)

As far as I can tell this requires one query for the categories then while iterated through the categories, I need to make 2 page query (eg. SELECT * FROM pages P WHERE P.category = 1). Finally, for each page I need to make a section query (3 in total). This means I have to make 6 queries just to get the data above. Is there a better way?

Upvotes: 0

Views: 69

Answers (2)

Luis LL
Luis LL

Reputation: 2993

Due to lack of schema information...

I'm assuming that you have three tables, with a schema like:

Categories
    CategoryID (int)
    CategoryName (varchar)
    CategoryDisplayOrder (int)

Pages 
    PageID (int)
    PageCode (varchar)
    PageDisplayOrder (int)

Sections
    SectionID (int)
    SectionNumber (varchar)
    SectionDisplayOrder (int)

your query would look like: 

SELECT CategoryName, PageCode, SectionNumber
FROM Categories
INNER JOIN Pages ON Categories.CategoryID = Pages.CategoryID
INNER JOIN Sections ON Pages.PageID = Sections.PageID
ORDER BY CategoryDisplayOrder, PageDisplayOrder, SectionDisplayOrder

Upvotes: 1

user240141
user240141

Reputation:

Assuming your table's schema like this:

Id Name ParentId, for Grand or Top most set parent =0 or -1

Query will look like:

with [CTE] as (
    select * from [TheTable] c where c.[ParentId] = 1
    union all
    select * from [CTE] p, [TheTable] c where c.[ParentId] = p.[Id]
)
select * from [CTE]

Upvotes: 0

Related Questions