Re: SQL question

"Uwe C. Schroeder" <uwe(at)oss4u(dot)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