I would like to know if and how it is possible to join the *result* of
a union with another table (without first doing individual joins to
each part of the result that then will be combined into a union) ??
For example, I would like to do such a join, if possible, to avoid
doing something like this:
select tableA.col1 as c1 , tableC.col2, tableC.col3, tableC.col4,
tableC.col5, .....
from tableA inner join tableC
on tableA.col1 = tableC.col1
union
select tableB.col1 as c1, tableC.col2, tableC.col3, tableC.col4,
tableC.col5, .....
from tableB inner join tableC
on tableB.col1 = tableC.col1
The problem with the code above is that I will have a lot of wanted
output fields which in the code will be duplicated from tableC in each
part of the union statement, and if I in the future will add a field
it will then have to be added in two places.
Instead of the code above I would like to do extract the output
columns from tableC to only one place, with code something like this:
select c1 , tableC.col2, tableC.col3, tableC.col4, tableC.col5, .....
from tableC inner join
(
select tableA.col1 as c1 from tableA
union
select tableB.col1 as c1 from tableB
) as VirtualUnionTable
on tableC.col1 = VirtualUnionTable.c1
but this does not work (at least not with mysql 4.0.13) and I don't
know how to refer to the result of the union and the join column of
that union (as you can see above I tried to name it with "as
VirtualUnionTable" and then "on... = VirtualUnionTable.c" but it does
not work).
As I mentioned, the purpose was to eleminate the duplication of all
columns from tableC, which I will have to do if I join tableC
individually to the selects which are then combined into a union, as
in the first example above.
Is it even possible to make a join to the result of a union without
doing the joins separately first, and can someone show how that kind
of code would look like ?
/ Tomas
