Re: subselects?

Knut,

> In a table (T1) there are several columns. Two of them together make up a
> link to any row in the same database: one column (T1C5) is a string
> representing the name of a table, and the other (T1C6) an int representing
> the id of a row in the table referred to in T1C5.
> I want to write a query returning some columns from T1,
> (T1C1,T1C2,T1C3,T1C4), and in the same result row, all of the columns from
> the table with its name in T1C5 with and id in T1C6.
> A simple select query using a join like (SELECT * FROM a LEFT JOIN b ON
> (a.ref = b.id);) could be used if all the table names in the T1C5 column
> were the same. However, they are not. Any suggestions on how to solve this
> problem?

Unless your referents are as few as 3 tables, then I'd actually recommend a
union for this:

SELECT T1.*, T3.Col1, T3.Col2, T3.Col3
FROM T1 JOIN T3 ON (T1.C6 = T3.id AND T1.C5 = 'T3')
UNION ALL
SELECT T1.*, T4.Col1, T4.Col2, T4.Col3
FROM T1 JOIN T4 ON (T1.C6 = T4.id AND T1.C5 = 'T4')
etc.

You could also work through left-outer-join subselects, but I think the UNION
method is faster and easier to write.

If you're talking about a large number of referent tables, no matter how you
do it your query is going to be fairly slow. You also have the problem that
the referent tables all need compatible data types in the columns you want to
display.

Overall, I think you need to take a hard look at your schema and decide
whether things really need to be structured this way ....

--
-Josh Berkus
Aglio Database Solutions
San Francisco