Re: determining which table to lookup depending on data values

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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!