Обсуждение: where clause subqueries vs multiple rows results

Поиск
Список
Период
Сортировка

where clause subqueries vs multiple rows results

От
James Cloos
Дата:
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

Re: where clause subqueries vs multiple rows results

От
"Aaron Bono"
Дата:
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
==================================================================

Re: where clause subqueries vs multiple rows results

От
"Aaron Bono"
Дата:
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
==================================================================

Re: where clause subqueries vs multiple rows results

От
Richard Broersma Jr
Дата:
> 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.

Re: where clause subqueries vs multiple rows results

От
James Cloos
Дата:
[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