pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLocating all Children given Set of Parents
7+ messages / 2 participants
[nested] [flat]
* Locating all Children given Set of Parents
@ 2003-05-13 16:19 Adam Sherman <adam@tritus.ca>
2003-05-13 16:47 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Adam Sherman @ 2003-05-13 16:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locating all Children given Set of Parents
2003-05-13 16:19 Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
@ 2003-05-13 16:47 ` Josh Berkus <josh@agliodbs.com>
2003-05-13 17:46 ` Re: Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
2003-05-13 17:50 ` Re: Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
0 siblings, 2 replies; 7+ messages in thread
From: Josh Berkus @ 2003-05-13 16:47 UTC (permalink / raw)
To: Adam Sherman <adam@tritus.ca>; pgsql-sql
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_child
where pc.child_id = children.id
and pc.parent_id IN ($parent_id_list)
);
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locating all Children given Set of Parents
2003-05-13 16:19 Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
2003-05-13 16:47 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
@ 2003-05-13 17:46 ` Adam Sherman <adam@tritus.ca>
2003-05-13 19:01 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 7+ messages in thread
From: Adam Sherman @ 2003-05-13 17:46 UTC (permalink / raw)
To: pgsql-sql; +Cc: Josh Berkus <josh@agliodbs.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locating all Children given Set of Parents
2003-05-13 16:19 Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
2003-05-13 16:47 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
2003-05-13 17:46 ` Re: Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
@ 2003-05-13 19:01 ` Josh Berkus <josh@agliodbs.com>
2003-05-13 19:19 ` Re: Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
0 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2003-05-13 19:01 UTC (permalink / raw)
To: Adam Sherman <adam@tritus.ca>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locating all Children given Set of Parents
2003-05-13 16:19 Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
2003-05-13 16:47 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
2003-05-13 17:46 ` Re: Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
2003-05-13 19:01 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
@ 2003-05-13 19:19 ` Adam Sherman <adam@tritus.ca>
2003-05-13 19:30 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Adam Sherman @ 2003-05-13 19:19 UTC (permalink / raw)
To: josh@agliodbs.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locating all Children given Set of Parents
2003-05-13 16:19 Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
2003-05-13 16:47 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
2003-05-13 17:46 ` Re: Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
2003-05-13 19:01 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
2003-05-13 19:19 ` Re: Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
@ 2003-05-13 19:30 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2003-05-13 19:30 UTC (permalink / raw)
To: Adam Sherman <adam@tritus.ca>; pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locating all Children given Set of Parents
2003-05-13 16:19 Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
2003-05-13 16:47 ` Re: Locating all Children given Set of Parents Josh Berkus <josh@agliodbs.com>
@ 2003-05-13 17:50 ` Adam Sherman <adam@tritus.ca>
1 sibling, 0 replies; 7+ messages in thread
From: Adam Sherman @ 2003-05-13 17:50 UTC (permalink / raw)
To: pgsql-sql; +Cc: Josh Berkus <josh@agliodbs.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-05-13 19:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-13 16:19 Locating all Children given Set of Parents Adam Sherman <adam@tritus.ca>
2003-05-13 16:47 ` Josh Berkus <josh@agliodbs.com>
2003-05-13 17:46 ` Adam Sherman <adam@tritus.ca>
2003-05-13 19:01 ` Josh Berkus <josh@agliodbs.com>
2003-05-13 19:19 ` Adam Sherman <adam@tritus.ca>
2003-05-13 19:30 ` Josh Berkus <josh@agliodbs.com>
2003-05-13 17:50 ` Adam Sherman <adam@tritus.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox