Query question

Hello,

I have 3 tables, A, B and C. There is a one-to many relationship between A and B and C and B. I'm trying to write a query which will get out all records in A and B for a given record in C, plus those which also *don't* exist in C.

E.g. in A:

a_id a_text
---- ------

1       a_one
2       a_two
3       a_three

In C:

c_id c_text
---- ------

1 c_one

In B:

b_id b_text a_id c_id
---- ------ ---- ----

1       b_one     1       1
2       b_three   3       1

I've tried:

SELECT  B.b_text FROM A, B
WHERE   A.a_id = B.a_id(+)
AND     B.c_id = 1

... but this only displays "b_one" and "b_three". I'd also like to see a blank record for the missing record in A (a_id = 2). I understand I'm on the "wrong end" of an outer join, but wondered whether there was any way around this. The only way that I could think of was to SELECT DISTINCT a_id FROM A and MINUS all a_ids in B WHERE c_id = 1. However, the tables involved are likely to become large and as such will obviously degrade query performance.

Thanks for any thoughts.

Mark Received on Fri May 21 2004 - 05:37:08 CDT