----- Original Message ----- From: Randall Parker <rand...@nls.net> To: <win...@lists.mysql.com> Sent: Monday, April 24, 2000 4:10 PM Subject: Outer join question

I'm stuck on how to write an outer join to get all the rows in table CC that are not linked (via link table BB) to a particular row in AA.

I have 3 tables. I'll call them AA, BB, and CC. BB is a linking table between AA and CC. Basically a record in CC can be linked to 1 or more records in AA. In my example below I'm making AA have a range of counter field fields that is different than the range in CC just so it is clear which table I'm referring to when testing a particular field.

AA has AAPriKey with rows: 1 2 3

BB has 2 fields AALink and CCLink with the following rows and values: 1 24 1 25 2 20 2 21 2 23 3 24

CC has CCPriKey with rows: 20 21 22 23 24 25 26

So, for instance CC row 23 is mapped to both AA row 1 and AA row 3. CC row 24 is also mapped to AA row 1.

What I want is a query that shows me all the rows in CC that are not already mapped to a particular row in AA. eg I want all the rows in CC that are not mapped (via BB) to row 1 in AA.

I think I need to use an OUTER JOIN but it is not clear to me that this can be done in a single SELECT statement. I don't think I need to use table AA in the query since I can join between CC and BB and then test the AALink field in the table BB.

My problem is that I want all the rows in CC that are not linked to _any_ row in AA as well as all the rows in CC that are linked to all but one row in AA.

So for BB I don't want the rows that have AALink = 1. But I want to outer join CC to BB since I also want the cases where the BB is null.

But in the cases where BB is null I do no want to pick up CC rows that also link to BB rows.