Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help formulating multi-table SELECT statement

Help formulating multi-table SELECT statement

From
Phil Mitchell
Date:
I have a simple table model for a many-many r'ship between keys and sigs:

KEYS table has columns: key, keyID
SIGS table has columns: sig, sigID
KEYS_SIGS has columns: keyID, sigID

For a given key X, how can I retrieve all the sigs that are related to it?
Conceptually, I need three SELECTS:

#1. SELECT keyID FROM keys
WHERE key = X

#2. SELECT sigID FROM keys_sigs
WHERE keyID = (result of #1)

SELECT sig FROM sigs
WHERE sigID = (result of #2)

I tried nesting these queries, but PG complained that my subselect had
produced multiple values -- which of course it can.

Re: Help formulating multi-table SELECT statement

From
Phil Mitchell
Date:
At 02:53 PM 2/26/02, you wrote:
>Hey Phil,
>
>Not sure if this will work..don't have any tables like that to test this
>on but try this:
>
>SELECT sig FROM sigs WHERE sigID=(SELECT sigID FROM keys_sigs WHERE
>keyID=(SELECT keyID FROM keys WHERE key = X));

Travis, I had tried nesting the selects like that, but postgres apparently
complains when subselects return multiple values.

Re: Help formulating multi-table SELECT statement

From
"Travis Hoyt"
Date:
What values in your tables are unique?  Are there many keyID's for the
same key or the reverse?  And the same question for the keys_sigs table.
Can you give an example of your data?  I would guess that the two inner
selects should not return multiple rows.  Maybe I'm not getting the
picture.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Phil Mitchell
Sent: Tuesday, February 26, 2002 3:39 PM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Help formulating multi-table SELECT statement


At 02:53 PM 2/26/02, you wrote:
>Hey Phil,
>
>Not sure if this will work..don't have any tables like that to test this
>on but try this:
>
>SELECT sig FROM sigs WHERE sigID=(SELECT sigID FROM keys_sigs WHERE
>keyID=(SELECT keyID FROM keys WHERE key = X));

Travis, I had tried nesting the selects like that, but postgres apparently
complains when subselects return multiple values.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Attachment

Re: Help formulating multi-table SELECT statement

From
"Stephan Szabo"
Date:
> I have a simple table model for a many-many r'ship between keys and sigs:
>
> KEYS table has columns: key, keyID
> SIGS table has columns: sig, sigID
> KEYS_SIGS has columns: keyID, sigID
>
> For a given key X, how can I retrieve all the sigs that are related to it?
> Conceptually, I need three SELECTS:
>
> #1. SELECT keyID FROM keys
> WHERE key = X
>
> #2. SELECT sigID FROM keys_sigs
> WHERE keyID = (result of #1)
>
> SELECT sig FROM sigs
> WHERE sigID = (result of #2)
>
> I tried nesting these queries, but PG complained that my subselect had
> produced multiple values -- which of course it can.

Right, because it wants a scalar subquery on the right side.  If you want
to say is this value among the results, you probably want IN (or =ANY)
not an =.  However, you probably just want joins...
SELECT sigs.sig FROM keys, keys_sigs, sigs WHERE
 keys.key=X and keys_sigs.keyID=keys.keyID and
 sigs.sigID=keys_sigs.sigID;
Will I think do what you want.


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com