>>>>> "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
Hi!
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;
Regards,
Monty
