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@xxxxxxxxxx> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
