MySQL JOIN based on highest date and non-unique columns
I need some help with a MySQL query I'm working on. I have data as follows.
Table 1
id date1 text number
---|------------|--------|-------
1 | 2012-12-12 | hi | 399
2 | 2011-11-11 | so | 399
5 | 2010-10-10 | what | 555
3 | 2009-09-09 | bye | 300
4 | 2008-08-08 | you | 300
Table 2
id number date2 ref
---|--------|------------|----
1 | 399 | 2012-06-06 | 40
2 | 399 | 2011-06-06 | 50
5 | 555 | 2011-03-03 | 60
For each row in Table 1, I want to get zero or one ref values from Table 2. There should be a row in the result for each row in Table 1. The number column isn't unique to either table, so the join must be made using the date1 & date2 columns, where date2 is the highest value for the number without exceeding date1 for that number.
The desired result from the above example would be like so.
date1 text number ref
------------|--------|--------|-----
2012-12-12 | hi | 399 | 40
2011-11-11 | so | 399 | 50
2010-10-10 | what | 555 | null
2009-09-09 | bye | 30开发者_开发知识库0 | null
2008-08-08 | you | 300 | null
You can see in the result's first row, ref is 40 was chosen because in table2 the record with ref=40 had a date2 that that was less than date1, and the highest date that met that condition. In the result's second row, ref is 50 was chosen because in table2 the record with ref=50 had a date2 that that was less than date1, and the highest date that met that condition. The rest of the results have null refs because date1 is always less or a corresponding number doesn't exist in table2.
I've got to a certain point but I'm stuck. The query I have so far is like this.
SELECT date1, text, number, ref
FROM table1
LEFT JOIN (
SELECT *
FROM (
SELECT *
FROM table2
WHERE date2 <= '2012-12-12'
ORDER BY date2 DESC
) tmp
GROUP BY msisdn
) tmp ON table1.number = table2.number;
The problem is that the hard coded date won't do, it should be based on date1, but I can't use date1 because it's in the outer query. Is there a way I can make this work?
I tried similar example with different tables just now and was able to get what you wanted. Below is a similar query modified to fit your needs. You might want to change <
with <=
if that is what you are looking for.
SELECT a.date1, a.text, b.ref
FROM table1 a LEFT JOIN table2 b ON
( a.number = b.number
AND a.date1 > b.date2
AND b.date2 = ( SELECT MAX(x.date2)
FROM table2 x
WHERE x.number = b.number
AND x.date2 < a.date1)
)
Untested:
SELECT t1.date1,
t1.text,
t1.number,
(SELECT a.ref
FROM TABLE_2 a
JOIN (SELECT t.number,
MAX(t.date2) AS max_date
FROM TABLE_2 t
WHERE t.number = t1.number
AND t.date2 <= t1.date1
GROUP BY t.number) b ON b.number = a.number
AND b.max_date = a.date2)
FROM TABLE_1 t1
The issue is the use of t1
in the derived table of the subselect...
精彩评论