Click here to Skip to main content
15,065,044 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
OK this is the data I am working with:

category_child_id category_parent_id
                1                  0
                2                  0
                3                  1
                4                  1
                5                  3
                6                  3
                7                  4
                8                  0
                9                  8
               10                  8
               11                  0
               12                 11
               13                 11
               14                  0
               15                 14
               16                 14
               17                 14
               18                  0
               19                 18
               20                 18
               21                 18
                0                 19

It's basically categories with sub categories etc etc.

If I

SELECT category_child_id FROM category_xref WHERE category_parent_id = 1
it returns 3 & 4 which is correct. However there are no products in this category only in the category below so the results I actually want are 5 & 6 as well. However this is not always the same so it does need to be a query.

So basically I need to run a query to get all connected(nested) categories from the table. I've tried many ways with failed results so any help would be great.
Posted

1 solution

Try this

SQL
select B.category_child_id from category_xref A inner join category_xref B
on A.category_child_id = B.category_parent_id
where (A.category_parent_id = 1 or B.category_parent_id = 1)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900