Jump to content
funbinod

problem with parent-child rows selection

Recommended Posts

hello all!

i am struggling with a problem in mysql.

i want to select all rows from a table with an id and its child ids.

i tried the following

............... AND projtype IN (SELECT id FROM projcat WHERE under=1 UNION SELECT id FROM projcat WHERE under IN (SELECT id FROM projcat WHERE under=1))

it works. But this selects rows that matched the child ids only. i want to select all the rows which use the parent id also (i.e. '1' on the given example).

please help me with some hints.

 

thanks in advance....

Share this post


Link to post
Share on other sites

You need more unions to select each level of children if you want to do it all in SQL.  Obviously, you're limited to a maximum depth with that.  There are other ways to design tables and store paths where you could easily select all descendants or ancestors of a particular node.

https://www.slideshare.net/billkarwin/models-for-hierarchical-data

You're using the first example he gives.

  • Like 1

Share this post


Link to post
Share on other sites
15 hours ago, justsomeguy said:

You need more unions to select each level of children if you want to do it all in SQL.  Obviously, you're limited to a maximum depth with that.  There are other ways to design tables and store paths where you could easily select all descendants or ancestors of a particular node.

https://www.slideshare.net/billkarwin/models-for-hierarchical-data

You're using the first example he gives.

thank you for the reply.

but it banged my head. this is beyond my practice and is very tough to understand. can't it be done with a simple modification to my code???? like........

.......... AND projtype IN (1, SELECT id FROM projcat WHERE under=1 UNION SELECT id FROM projcat WHERE under IN (SELECT id FROM projcat WHERE under=1))

thank u again.

Share this post


Link to post
Share on other sites
17 hours ago, justsomeguy said:

You need more unions to select each level of children if you want to do it all in SQL. 

this hint solved my problem.

i cheated the code like below.

.......... AND projtype IN (SELECT id FROM projcat WHERE under=1 UNION SELECT id FROM projcat WHERE id=1 UNION SELECT id FROM projcat WHERE under IN (SELECT id FROM projcat WHERE under=1))

thank u very very much...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×