开发者

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
)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜