pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhere clause subqueries vs multiple rows results
5+ messages / 3 participants
[nested] [flat]
* where clause subqueries vs multiple rows results
@ 2006-09-11 03:27 James Cloos <cloos@jhcloos.com>
2006-09-11 04:54 ` Re: where clause subqueries vs multiple rows results Aaron Bono <postgresql@aranya.com>
2006-09-11 05:01 ` Re: where clause subqueries vs multiple rows results Richard Broersma Jr <rabroersma@yahoo.com>
2006-09-11 12:04 ` Re: where clause subqueries vs multiple rows results James Cloos <cloos@jhcloos.com>
0 siblings, 3 replies; 5+ messages in thread
From: James Cloos @ 2006-09-11 03:27 UTC (permalink / raw)
To: pgsql-sql
I've a query which I'd have liked to word akin to:
SELECT guid FROM child WHERE the_fkey =
( SELECT id FROM parent WHERE name ~ 'some_regex' )
ORDER BY the_fkey, my_pkey;
I got around it by doing the SELECT id first, and then doing a SELECT
guid for each row returned, appending the results together.
Can that be done in a single query, insead of 1+n queries?
Thanks,
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 0xED7DAEA6
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: where clause subqueries vs multiple rows results
2006-09-11 03:27 where clause subqueries vs multiple rows results James Cloos <cloos@jhcloos.com>
@ 2006-09-11 04:54 ` Aaron Bono <postgresql@aranya.com>
2006-09-11 04:55 ` Re: where clause subqueries vs multiple rows results Aaron Bono <postgresql@aranya.com>
2 siblings, 1 reply; 5+ messages in thread
From: Aaron Bono @ 2006-09-11 04:54 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; +Cc: pgsql-sql
On 9/10/06, James Cloos <cloos@jhcloos.com> wrote:
>
> I've a query which I'd have liked to word akin to:
>
> SELECT guid FROM child WHERE the_fkey =
> ( SELECT id FROM parent WHERE name ~ 'some_regex' )
> ORDER BY the_fkey, my_pkey;
>
> I got around it by doing the SELECT id first, and then doing a SELECT
> guid for each row returned, appending the results together.
>
> Can that be done in a single query, insead of 1+n queries?
Will this work?
SELECT guid
FROM child
INNER JOIN parent ON (
child.the_fkey = parent.id
AND
)
WHERE name ~ 'some_regex'
ORDER BY
child.the_fkey,
child.my_pkey
;
I am not sure what table name is from but since you say it is n+1 queries I
assume it is from the child table? It would help to know what columns are
on which tables.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: where clause subqueries vs multiple rows results
2006-09-11 03:27 where clause subqueries vs multiple rows results James Cloos <cloos@jhcloos.com>
2006-09-11 04:54 ` Re: where clause subqueries vs multiple rows results Aaron Bono <postgresql@aranya.com>
@ 2006-09-11 04:55 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 5+ messages in thread
From: Aaron Bono @ 2006-09-11 04:55 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; +Cc: pgsql-sql
Oops, see correction below...
On 9/10/06, Aaron Bono <postgresql@aranya.com> wrote:
>
> On 9/10/06, James Cloos <cloos@jhcloos.com> wrote:
>
> > I've a query which I'd have liked to word akin to:
> >
> > SELECT guid FROM child WHERE the_fkey =
> > ( SELECT id FROM parent WHERE name ~ 'some_regex' )
> > ORDER BY the_fkey, my_pkey;
> >
> > I got around it by doing the SELECT id first, and then doing a SELECT
> > guid for each row returned, appending the results together.
> >
> > Can that be done in a single query, insead of 1+n queries?
>
>
>
> Will this work?
>
> SELECT guid
> FROM child
> INNER JOIN parent ON (
> child.the_fkey = parent.id
> )
> WHERE name ~ 'some_regex'
> ORDER BY
> child.the_fkey,
> child.my_pkey
> ;
>
> I am not sure what table name is from but since you say it is n+1 queries
> I assume it is from the child table? It would help to know what columns are
> on which tables.
>
> ==================================================================
> Aaron Bono
> Aranya Software Technologies, Inc.
> http://www.aranya.com
> http://codeelixir.com
> ==================================================================
>
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: where clause subqueries vs multiple rows results
2006-09-11 03:27 where clause subqueries vs multiple rows results James Cloos <cloos@jhcloos.com>
@ 2006-09-11 05:01 ` Richard Broersma Jr <rabroersma@yahoo.com>
2 siblings, 0 replies; 5+ messages in thread
From: Richard Broersma Jr @ 2006-09-11 05:01 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; pgsql-sql
> I've a query which I'd have liked to word akin to:
>
> SELECT guid FROM child WHERE the_fkey =
> ( SELECT id FROM parent WHERE name ~ 'some_regex' )
> ORDER BY the_fkey, my_pkey;
>
> I got around it by doing the SELECT id first, and then doing a SELECT
> guid for each row returned, appending the results together.
>
> Can that be done in a single query, insead of 1+n queries?
select guid
from child C join parent P
on (C.the_fkey = P.di)
Where P.name ~ 'some_regex'
order by C.the_fkey, P.my_pkey;
Perhaps this might work.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: where clause subqueries vs multiple rows results
2006-09-11 03:27 where clause subqueries vs multiple rows results James Cloos <cloos@jhcloos.com>
@ 2006-09-11 12:04 ` James Cloos <cloos@jhcloos.com>
2 siblings, 0 replies; 5+ messages in thread
From: James Cloos @ 2006-09-11 12:04 UTC (permalink / raw)
To: pgsql-sql
[SIGH]
I was getting the syntax wrong. Just using ON rathar than = fixed
the mistake.
Time to crash for the night (day?) it seems....
Thanks for the replies; if I hadn't've figured it out myself they
would have pushed me in the right direction.
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 0xED7DAEA6
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-09-11 12:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-11 03:27 where clause subqueries vs multiple rows results James Cloos <cloos@jhcloos.com>
2006-09-11 04:54 ` Aaron Bono <postgresql@aranya.com>
2006-09-11 04:55 ` Aaron Bono <postgresql@aranya.com>
2006-09-11 05:01 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-09-11 12:04 ` James Cloos <cloos@jhcloos.com>
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