Обсуждение: determining which table to lookup depending on data values

Hello all-

    I'm working with a poorly-designed schema and need to do a lookup in one table who's name I have to pull from a second table. I'm wondering if its possible to do something like this in PostgreSQL:


Say I have three tables:

CREATE TABLE audio (id int4, name varchar(32));
CREATE TABLE video (id int4, name varchar(32));
CREATE TABLE media (id int4, table_name varchar(32), table_id int4);

with data:

INSERT INTO audio VALUES (0, 'file.wav');
INSERT INTO video VALUES (0, 'file.avi');
INSERT INTO media VALUES (0, 'audio', 0);
INSERT INTO media VALUES (1, 'video', 0);


    Is there any way, especially in a single statement, where I can get the name of a few if I only have the media table's id?

SELECT name FROM (SELECT table_name FROM media WHERE media_id=1);

    Of course I get back an error about needing to give my subquery an alias, but even if I do so I only get the output from the subquery, instead of being able to tell PostgreSQL to use that output as the name of the table it should use to look up "name"

    The original design was meant for a multimedia play which could use a mixture of audio and video in a playlist, and that which housed all of the entries for the playlist could just refer to an id for the media table. When the software plays it has to pull this information out and do a single lookup for each item. This looses referential integrity and means you can't get from a play down to a specific file with one query.


Thanks in advance!

Steve Castellotti

Steve Castellotti wrote:
> SELECT name FROM (SELECT table_name FROM media WHERE media_id=1);

You can try something like:

SELECT name FROM audio JOIN media ON id
WHERE table_name='audio' AND media_id=1
UNION ALL
SELECT name FROM video JOIN media ON id
WHERE table_name='video' AND media_id=1
;

Assuming you've got indexes on (id) on both tables that should be
reasonably quick.

--
   Richard Huxton
   Archonet Ltd

On Sun, Dec 17, 2006 at 08:14:43PM +1300, Steve Castellotti wrote:
>
>    Hello all-
>        I'm working with a poorly-designed schema and need to do a lookup in one
>    table who's name I have to pull from a second table. I'm wondering if its
>    possible to do something like this in PostgreSQL:
>    Say I have three tables:
>    CREATE TABLE audio (id int4, name varchar(32));
>    CREATE TABLE video (id int4, name varchar(32));
>    CREATE TABLE media (id int4, table_name varchar(32), table_id int4);
>    with data:
>    INSERT INTO audio VALUES (0, 'file.wav');
>    INSERT INTO video VALUES (0, 'file.avi');
>    INSERT INTO media VALUES (0, 'audio', 0);
>    INSERT INTO media VALUES (1, 'video', 0);
>        Is there any way, especially in a single statement, where I can get the
>    name of a few if I only have the media table's id?
>    SELECT name FROM (SELECT table_name FROM media WHERE media_id=1);
>        Of course I get back an error about needing to give my subquery an
>    alias, but even if I do so I only get the output from the subquery, instead
>    of being able to tell PostgreSQL to use that output as the name of the table
>    it should use to look up "name"
>        The original design was meant for a multimedia play which could use a
>    mixture of audio and video in a playlist, and that which housed all of the
>    entries for the playlist could just refer to an id for the media table. When
>    the software plays it has to pull this information out and do a single
>    lookup for each item. This looses referential integrity and means you can't
>    get from a play down to a specific file with one query.
>    Thanks in advance!
>    Steve Castellotti

Here's a sketch of what you can do about this.  Let me know whether
any of it's unclear :)

http://archives.postgresql.org/sfpug/2005-04/msg00022.php

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!