left join in case of....

[Quarco]

Hey,

Suppose you have a table_ex like:

id nuber1 chr ( = a or b)
1 1000 a
2 1001 a
3 1002 b
4 1003 a
etc..

Now, is it possible to have a query like:

LEFT JOIN tablea if chr=a ON table_ex.number1=tablea.id
OR
LEFT JOIN tableb if chr=b ON table_ex.number1=tableb.id

Uhm.. .. or something like that :-)

Thanx in advance anyway..

[Bill Karwin]

Quarco wrote:

Now, is it possible to have a query like:

LEFT JOIN tablea if chr=a ON table_ex.number1=tablea.id
OR
LEFT JOIN tableb if chr=b ON table_ex.number1=tableb.id

Yes, you can put other terms in the join condition, even if they aren't
strictly related to the columns on which the join is defined.

In your case, I infer that you're using chr to dictate which table a
given record in table_ex should join. I think you need to do two left
joins. The syntax could look like this:

SELECT ...
FROM table_ex T
LEFT JOIN tablea ON (T.number1 = tablea.id AND T.chr='a')
LEFT JOIN tableb ON (T.number1 = tableb.id AND T.chr='b');

In a way, you _are_ joining to both tablea and tableb for every record
in table_ex, but no rows in tablea/tableb match the condition for the
given record, if the second term in the condition is false. It's a
little bit hard to get one's brain wrapped around that concept, but it
works!

The parentheses are not required, I just though it makes it more clear
that both terms are part of the same condition.

Bill K.