Select COLUMN name from one table based on VALUE from another in mySQL?
<p>If I have <code>JOBS</code> with these values, let's say:</p>

<pre><code>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
</code></pre>

<p>and another table <code>JOBS_PREFS</code> that has something like this:</p>

<pre><code>id|rate_a|rate_b|rate_c
1|50|60|70
2|60|60|70
3|50|50|75
</code></pre>

<p>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.</p>

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

<p>Something like this:</p>

<pre><code>SELECT *,
       CASE WHEN JOBS.is_hourly &gt; 0
            THEN JOBS.hours * JOBS_PREFS.JOBS.rate_type
            ELSE JOBS_PREFS.JOBS.rate_type
       END cost
FROM ...
</code></pre>

<p>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).</p>

<p>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.</p>

<p>Makes sense?</p>

<p>Doable?</p>