From: | Oleg Lebedev <olebedev(at)waterford(dot)org> |
---|---|
To: | Postgres SQL Mailing List <pgsql-sql(at)postgresql(dot)org> |
Subject: | sequential joins |
Date: | 2002-03-01 20:21:51 |
Message-ID: | 3C7FE2DF.2AF0A2BC@waterford.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2002-03-01 20:28:46 | Re: sequential joins |
Previous Message | Josh Berkus | 2002-03-01 17:42:35 | Re: sub SELECT |