From: Greg Stark <gsstark@mit.edu>
To: pgsql-general@postgresql.org
Subject: Re: SQL question
Date: 07 Nov 2004 15:16:58 -0500
Message-ID: <87is8hl8hx.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200411061513.25914.uwe@oss4u.com>
References: <200411061513.25914.uwe@oss4u.com>
"Uwe C. Schroeder" <uwe@oss4u.com> writes:
> Here's a question for the SQL guru's out there, which I've been trying to
> solve for the last couple of hours. There's got to be a solution to this, but
> somehow I can't find it.
>
> Tables:
>
> table1 (
> uid int PK,
> uname varchar(64)
> )
>
> table2 (
> uid int FK to table1,
> xuid int FK to table 1
> )
>
> table3 (
> uid int FK to table1,
> yuid int FK to table1
> )
SELECT *
FROM table2
FULL OUTER JOIN table3 ON (table2.uid = tabletable3.uid AND xuid = yuid)
JOIN table1 USING (uid)
Or if you find it clearer
SELECT *
FROM table1
JOIN (table2 FULL OUTER JOIN table 3 ON (table2.uid = tabletable3.uid AND xuid = yuid)) USING (uid)
You might want to have an outer join (RIGHT OUTER JOIN in the first form, LEFT
OUTER JOIN in the second) if you want to list records from table1 for which
there are no matching records in table2 or table3.
This is going to be a hard query to get to be fast though.
The other alternative would be to do a self-join of table1 to table1 and then
use subqueries to check for matching table2 or table3 entries. This would be a
lose if the relationships are relatively sparse, but if you have more tables
it might end up being a win, I don't know.
Something like
SELECT table1.*,
(select xuid from table2 where uid = child.uid) as xuid,
(select yuid from table3 where uid = child.uid) as yuid,
(select zuid from table4 where uid = child.uid) as zuid,
...
FROM table1
CROSS JOIN table1 AS child
--
greg
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: gsstark@mit.edu
Subject: Re: SQL question
In-Reply-To: <87is8hl8hx.fsf@stark.xeocode.com>
References: <200411061513.25914.uwe@oss4u.com> <87is8hl8hx.fsf@stark.xeocode.com>
* 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