Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Join A to B via AB where AB may or may not join
SELECT *
FROM TABLE_AB AB
WHERE EXISTS (
SELECT AB.A
FROM TABLE_A
WHERE AB.A = TABLE_A.A)
UNION
SELECT A, B
FROM TABLE_A, TABLE_B
WHERE NOT EXISTS (
SELECT A, B
FROM TABLE_AB
WHERE TABLE_A.A = TABLE_AB.A)
ORDER BY 1,2
"Don69" <vaillancourt.don_at_gmail.com> wrote in message
news:d1b54f11-c50b-4c43-9e00-1f28be22d901_at_e23g2000prf.googlegroups.com...
>I have tables A and B which are joined in a binary relationship via
> AB.
>
> I need to:
>
> select *
> from a, b
>
> But the where cause must use the relationship defined in AB if one
> exists, otherwise join all B rows to every A row where the
> relationship doesn't exists.
>
> Given:
> A -> (A,B,C,D)
> B -> (1,2,3,4)
> AB -> (A1,A2,B3)
>
> Should result in:
> A1, A2, B3, C1, C2, C3, C4, D1, D2, D3, D4
>
> Can anyone help with this. I know I've done it before, but can't
> remember how.
>
> Thanks
Received on Fri Dec 14 2007 - 14:03:26 CST