Thread: Locating all Children given Set of Parents
I have three tables: parents, children & parent_child_mm. (So Parents and Children are in a Many-to-Many relationship.) Given a set of Parents, say 1, 2 & 3, how do I retrieve all children in an efficient manner? (I want each child to appear only once.) Thanks, A. -- Adam Sherman Tritus CG Inc. http://www.tritus.ca/ +1 (613) 797-6819
Adam, > I have three tables: parents, children & parent_child_mm. > > (So Parents and Children are in a Many-to-Many relationship.) > > Given a set of Parents, say 1, 2 & 3, how do I retrieve all children in an > efficient manner? (I want each child to appear only once.) Easy: Select children.* from children where exists (select pc_id from parent_childwhere pc.child_id = children.idand pc.parent_id IN ($parent_id_list)); -- Josh Berkus Aglio Database Solutions San Francisco
On 05/13/03 09:47:46 -0700 Josh Berkus wrote: > Select children.* > from children where exists (select pc_id > from parent_child > where pc.child_id = children.id > and pc.parent_id IN ($parent_id_list) > ); What is "pc_id" referring too? Thanks, A. -- Adam Sherman Tritus CG Inc. http://www.tritus.ca/ +1 (613) 797-6819
On 05/13/03 09:47:46 -0700 Josh Berkus wrote: > Select children.* > from children where exists (select pc_id > from parent_child > where pc.child_id = children.id > and pc.parent_id IN ($parent_id_list) > ); Great, that works fine. pc_id is not important. Thanks for your quick help! A. -- Adam Sherman Tritus CG Inc. http://www.tritus.ca/ +1 (613) 797-6819
Adam, > > Select children.* > > from children where exists (select pc_id > > from parent_child > > where pc.child_id = children.id > > and pc.parent_id IN ($parent_id_list) > > ); > > What is "pc_id" referring too? parent_child.id Which column you use isn't important, just don't use "SELECT *" -- -Josh BerkusAglio Database SolutionsSan Francisco
On 05/13/03 12:01:45 -0700 Josh Berkus wrote: > Which column you use isn't important, just don't use "SELECT *" Oh? I just did, and it worked fine. I take it this is a performance issue? Thanks again for all your help, A. -- Adam Sherman Tritus CG Inc. http://www.tritus.ca/ +1 (613) 797-6819
Adam, > Oh? I just did, and it worked fine. I take it this is a performance issue? Yes. If you use "SELECT *" in a WHERE EXISTS clause, you may cause the system to use more sort resources than are really necessary, especially if you have TOASTed or LO columns in the table. -- -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco