Re: Help formulating multi-table SELECT statement

> 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