CODEWITHSUNDEEP
mysql
Crazy Serb
Crazy Serb

Reputation: 76320

Select COLUMN name from one table based on VALUE from another in mySQL?

If I have JOBS with these values, let's say:

id|rate_type|hours|is_hourly
1|rate_a|2|1
2|rate_b|1|1
3|rate_a|3|1
4|rate_c|1|0

and another table JOBS_PREFS that has something like this:

id|rate_a|rate_b|rate_c
1|50|60|70
2|60|60|70
3|50|50|75

Basically one table lists 3 different rates for custom jobs users can specify (so they don't have to declare a custom rate each time), and the other table lists all their jobs with a reference to which one of those 3 custom rates should be used for each job.

How would I write a query that returns HOURS * RATE_TYPE from mySQL directly, instead of having to write PHP logic to do that.

Something like this:

SELECT *,
       CASE WHEN JOBS.is_hourly > 0
            THEN JOBS.hours * JOBS_PREFS.JOBS.rate_type
            ELSE JOBS_PREFS.JOBS.rate_type
       END cost
FROM ...

Don't worry about the logic behind it all and the table design, as this is a simplified version of what I am dealing with, and no, normalizing the table is not what I am looking for - this is for a small customization on a small database that will simplify things up a bit (unless the query itself takes a lot longer to generate than me running PHP logic on top of it to get "cost" in the end).

How do I reference the value from JOBS_PREFS.rate_a column, for example, based on the value "rate_a" being stored for that job ID in JOBS table? Basically, the column VALUE in JOBS table dictates the column NAME from which I'm pulling data out from JOBS_PREFS.

Makes sense?

Doable?

Upvotes: 2

Views: 1329

Answers (1)

John Boker
John Boker

Reputation: 83709

Something like this might work for you:

http://sqlfiddle.com/#!9/19eb5/5

select 
    id, rate_type, hours, hourly,

        (select 
            case j.rate_type 
               when 'rate_a' then p.rate_a
               when 'rate_b' then p.rate_b
               when 'rate_c' then p.rate_c
               else 0
            end * (case when hourly >= 1 then j.hours else 1 end) 
          from job_prefs p 
          where p.id = j.id 
          limit 1) money
from
    jobs j

Upvotes: 2

Related Questions