List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 23 1999 7:45pm
Subject:LEFT OUTER JOIN question
View as plain text  
>>>>> "John" == John Girard <jgirard01@stripped> writes:

John> We're working away on a project here with some interesting joins that we
John> can't quite wrap out minds around.

John> Here's how things are laid out:

John> Table1   Table2    Table3    Table4
John> -------|--------|----------|--------

John> ID ------ ID    /-->X
John> A         X ---/    K ------- K
John> B         Y         L         J
John> C         Z         M

John> What this means is we'd like to join Table1 to Table2 on ID, Table2 to
John> Table3 on X and Table3 to Table4 on K.  We'd like for the join on X to be an
John> outer join, and the others to be inners.

John> In the end, we need a result with all rows from the join on Table1 and
John> Table2, and nulls for the fields in Table3 and Table4 (X,K,L,M,J) IF there's
John> no join on X.  Of course, if there IS a join on X, we'd like to have the
John> fields for Table3 and Table4 populated appropriately.

John> My fear: we need subqueries here (though I've never used them myself, and
John> don't know enough to make such speculations).  It seems like what we're
John> doing is really an outer join on the result of two *separate* queries (the
John> query that joins Table1 to Table2 and the query that joins Table3 to
John> Table4).  We know we can make the whole thing work by doing another outer
John> join between Table3 and Table4, but would prefer to do it the "right" way if
John> at all possible.

John> Does anyone have any suggestions?

John> Thanks in advance,
John> John Girard


Doesn't the following work for you:

SELECT * from table1,table2 left join table3 using (X) left join
table4 USING(K) WHERE table1.ID=table2.ID;

LEFT OUTER JOIN questionJohn Girard23 Sep
  • LEFT OUTER JOIN questionMichael Widenius23 Sep