CODEWITHSUNDEEP
sqlmysql
malcomio
malcomio

Reputation: 711

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, sometimes 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

Upvotes: 2

Views: 141

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

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
)

Upvotes: 0

Rob Di Marco
Rob Di Marco

Reputation: 44962

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

Upvotes: 1

Related Questions