By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

left join in case of....

P: n/a
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..
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.