join where the specifying column depends on the results
I want to set up a join so that the following pseudocode would apply
SELECT * FROM client_rates cl, languages l
IF cl.Language is numeric // i.e. is not a specific language
join condition = WHERE cl.Language = l.category
ELSE
join condition = WHERE cl.Language = l.Language
Is it possible to achieve this just in (My)SQL?
There are other columns in these tables, but the relevant ones are as follows:
client_rates(CompanyID,Language, Rate)
languages(Language, Category)
in client_rates, so开发者_开发问答metimes Language is the specific language, but sometimes it is for a category. For example, for client 80, there is a rate for French, and a rate for English, which are both category 1 languages, but for some clients there are just rates for categories 1,2,and 3
Could you use a UNION? So basically do it like
SELECT * FROM client_rates cl, languages l join condition = WHERE cl.Language = l.category and cl.Language is numeric
UNION
SELECT * FROM client_rates cl, languages l join condition = WHERE cl.Language = l.Language and cl.Language is not numeric
That should work
Try something like this
SELECT * FROM client_rates as cl inner join languages as l
on
(cl.Language is numeric and cl.Language = l.category
or
cl.Language is not numeric and cl.Language = l.Language
)
精彩评论