JOINing 2 tables with LIMITations

[gin]

MySQL client version: 5.0.67

 

What I want:

I need to get the exchange rate applicable to a job, based on the date of the job. Each job will be assigned the exchange rate that fell on or before it's own date. Therefore:

Job 1 = rate 2.0

Job 2 = rate 2.2

 

Table `jobs`:
+----+------------+------+
| id | date       | cost |
+----+------------+------+
|  1 | 2011-12-01 | 100  |
|  2 | 2012-01-10 | 150  |
+----+------------+------+

Table `exchange`:
+------------+------+
| date       | rate |
+------------+------+
| 2011-09-15 | 1.9  |
| 2011-10-01 | 2.0  |
| 2011-12-05 | 2.1  |
| 2012-01-03 | 2.2  |
+------------+------+

 

What I have:

I have tried the code below, which works in one instance but not in the other

SELECT * FROM jobs
LEFT JOIN (
     SELECT * FROM exchange ORDER BY date DESC LIMIT 1
) tbl2 ON tbl2.date <= jobs.date
WHERE id=1;
+----+------------+------+------------+------+
| id | date       | cost | date       | rate |
+----+------------+------+------------+------+
|  1 | 2011-12-01 |  100 | NULL       | NULL |     <-- Oh noes!
+----+------------+------+------------+------+

SELECT * FROM jobs
LEFT JOIN (
     SELECT * FROM exchange ORDER BY date DESC LIMIT 1
) tbl2 ON tbl2.date <= jobs.date
WHERE id=2;
+----+------------+------+------------+------+
| id | date       | cost | date       | rate |
+----+------------+------+------------+------+
|  2 | 2012-01-10 |  150 | 2012-01-03 |  2.2 |     <-- Yay!
+----+------------+------+------------+------+

 

What I'm trying:

I'm guessing I need to add a WHERE in the subquery, but my attempt below throws an error.

SELECT * FROM jobs
LEFT JOIN (
     SELECT * FROM exchange WHERE date <= jobs.date ORDER BY date DESC LIMIT 1
) tbl2 ON tbl2.date <= jobs.date
WHERE id=1;

Error #1054 - Unknown column 'jobs.date' in 'where clause'

 

I could also remove the LIMIT like below, but that gets me more data than I need (and since this is a cut down of my actual data, there would be a LOT of data I don't need)

SELECT * FROM jobs
LEFT JOIN (
     SELECT * FROM exchange WHERE date <= jobs.date ORDER BY date DESC
) tbl2 ON tbl2.date <= jobs.date
WHERE id=1;

+----+------------+------+------------+------+
| id | date       | cost | date       | rate |
+----+------------+------+------------+------+
|  1 | 2011-12-01 |  100 | 2011-10-01 |  2.0 |     <-- Yay!
|  1 | 2011-12-01 |  100 | 2011-09-01 |  1.9 |     <-- Oh noes!
+----+------------+------+------------+------+

 

Any advice much appreciated.

[kickstart]

Hi

 

I think I would use a subselect to find the date of each rate, and the following date.

 

Something like this should do it:-

 

SELECT exchange.date, exchange.rate, MIN(exchange2.date) AS enddate
FROM exchange
LEFT OUTER JOIN exchange exchange2
ON exchange.date < exchange2.date
GROUP BY exchange.date, exchange.rate

 

Down side of this is that it brings back a null for the end date of the most recent item.

 

Integrating that into a query would give something like the following:-

 

SELECT *
FROM jobs
INNER JOIN (
SELECT exchange.date, exchange.rate, MIN(exchange2.date) AS enddate
FROM exchange
LEFT OUTER JOIN exchange exchange2
ON exchange.date < exchange2.date
exchange.date, exchange.rate) datesubquery
ON (jobs.date >= datesubquery.date
AND jobs.date < datesubquery.enddate)
OR (jobs.date >= datesubquery.date
AND datesubquery.enddate IS NULL)

 

Not tested (and for the real version don't use SELECT *) but hopefully gives you the idea

 

All the best

 

Keith

[mikosiko]

try this:

 

SELECT j.id,
       j.date,
       j.cost,
       (SELECT i.rate FROM exchange i
         WHERE i.date <= j.date
          ORDER BY i.date DESC LIMIT 1) AS therate
FROM jobs j;

[gin]

Thank you both for your advice! mikosiko's suggestion works, and is much easier for me to understand to boot I didn't know sub-queries could be used like that.