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
