Posted by Robert Klemme on 08/08/06 15:16

On 08.08.2006 16:31, jason.langdale@gmail.com wrote:
> I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5
> and table B has field 1,2,3,4,5. I want to do a union on these. (I have
> done so successfully if I stop here) I also want to join table C which
> has field 1,6,7,8,9. I would like to join on field 1 and bring in the
> other fields. I can join table C to A or B. I can union table A and B
> but I do not know how to both union A and B then join C. Can someone
> please help me? Thanks in advance.

What stops you from joining twice? Can't you just do

select 1,2,3,4,5,6,7,8,9
from tab_a, tab_c
where tab_a.1 = tab_c.1
union all
select 1,2,3,4,5
from tab_b, tab_c
where tab_b.1 = tab_c.1

Alternatively

select 1,2,3,4,5,6,7,8,9
from (
select 1,2,3,4,5
from tab_a
union all
select 1,2,3,4,5
from tab_b
) unioned, tab_c
where unioned.1 = tab_c.1
....

Of course you can also create a view for the union and then another one
for the join - this might be more easier to manage and handle. My guess
would be that the first approach is more efficient but this depends of
course on your data.

HTH

robert

[Back to original message]