Re: Query question

Mark wrote:

> 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

Above you stated you have three tables.
In your FROM clause you have only two tables In you WHERE clause you join only two tables

This is classwork so you need to get this yourself rather than have one of us hand it to you. But basically ... the problem here is that you haven't even made it to the level of putting the resources required into the SQL statement.

Start there ... then worry about the outer-joins, etc. later.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)