471,108 Members | 1,289 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

left join in case of....

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
1 3506
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.

Similar topics

2 posts views Thread by michael | last post: by
1 post views Thread by Paul Bramscher | last post: by
1 post views Thread by rossz | last post: by
3 posts views Thread by Ian Boyd | last post: by
5 posts views Thread by Sascha.Moellering | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.