From: Oleg Lebedev <olebedev@waterford.org>
To: Postgres SQL Mailing List <pgsql-sql@postgresql.org>
Subject: sequential joins
Date: Fri, 01 Mar 2002 13:21:51 -0700
Message-ID: <3C7FE2DF.2AF0A2BC@waterford.org> (raw)
You guys and gals were really helpful!
I hope you can help me with this problem too.
I have an Activity record, that has fields like artist, designer,
programmer containing ids of users from User table, which are assigned
to the current activity.
What I need to do is create a view, that would contain all the Activity
information, except artist, designer, and programmer should be filled
out with corresponding usernames from the User table.
Here is one way to do this:
Schemas:
Activity: name, artist, designer, programmer
User: username, objectid
SELECT name, artistname, designername, programmername
FROM
(SELECT *
FROM activity a
LEFT OUTER JOIN (SELECT username AS artistname, objectid AS userid FROM
User) n1
ON a.artist = n1.userid
LEFT OUTER JOIN (SELECT username AS designername, objectid AS userid
FROM User) n2
ON a.designer = n2.userid
LEFT OUTER JOIN (SELECT username AS programmername, objectid AS userid
FROM User) n3
ON a.programmer = n3.userid) names;
I wonder if there is a better way to do this. Maybe using CASE WHEN THEN
ELSE END clause to avoid multiple scans?
thanks,
Oleg
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olebedev@waterford.org
Subject: Re: sequential joins
In-Reply-To: <3C7FE2DF.2AF0A2BC@waterford.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox