0

I have this table:

person table
| id | name    |
|  1 | person1 |
|  2 | person2 |

person_grade table

| id | person_id | grade  | grade_date |
|  1 |     1     | grade1 | 2010-01-01 |
|  2 |     1     | grade2 | 2012-01-01 |
|  3 |     2     | grade3 | 2010-05-05 |
|  4 |     2     | grade4 | 2012-03-03 |

I want to know person grade at a specific time, say 2012-02-02. How to achieve this?

The closest I got was, with this query:

SELECT t1.id, t1.name, 
(SELECT grade FROM (
(SELECT s1.grade, s1.grade_date FROM person_grade AS s1 
WHERE s1.grade_date >= '2012-02-01'  AND s1.person_id = t1.id
ORDER BY s1.grade_date LIMIT 1)
UNION ALL
(SELECT s1.grade, s1.grade_date FROM person_grade AS s1 
WHERE s1.grade_date <= '2012-02-01'  AND s1.person_id = t1.id
ORDER BY s1.grade_date DESC LIMIT 1)
) AS ss ORDER BY grade_date LIMIT 1) AS grade_person
FROM person AS t1

But at MySQL that give me an error

"Unknown column 't1.id' in 'where clause'".

Please advise.
TIA

NoobEditor
  • 15,563
  • 19
  • 81
  • 112
mht
  • 1
  • You are missing an alias for the first inner query – Bohemian Jan 22 '14 at 06:07
  • Are you looking for all of the grades from a specific grade_date? – Barry Colebank Jr Jan 22 '14 at 06:07
  • @BarryColebankJr, I'm looking person's grade at specific date, not just grade_date. So at 2012-02-02 person1's grade should grade2 and person2's should grade3 – mht Jan 22 '14 at 08:23
  • @Bohemian, I think MySQL not compaling about missing alias. It's about outer table not recognize by subquery that put at FROM cluse. I saw correlation subquery that work, but not at FROM clause. – mht Jan 22 '14 at 08:38

4 Answers4

1
SELECT name,grade FROM person p
INNER JOIN person_grade pg
ON p.id=pg.person_id
WHERE pg.grade_date='2012-02-02'
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
  • Result nothing because I think there is no grade at that exact date. At 2012-02-02 person1's grade should grade2 and person2's grade shoud grade 3 – mht Jan 22 '14 at 08:12
0

I dont know about the syntax about the MySql but u can do something like this 

SELECT GRADE FROM person_grade WHERE DATE(GRADE_DATE,YEAR) = DATE(SEARCH_DATE,YEAR) AND DATE(GRADE_DATE,MONTH) = DATE(SEARCH_DATE,MONTH) AND DATE(GRADE_DATE,DAY) = DATE(SEARCH_DATE,DAY)
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
Rajendra B
  • 11
  • 5
  • I think that query for searching grade given date that match grade_date. Not what I looking for. – mht Jan 22 '14 at 08:27
0
SELECT p.name
     , pg.grade
FROM person p
     INNER JOIN person_grade pg ON p.id = pg.person_id
WHERE DATE(pg.grade_date) = '2012-02-02'

If this works where @mhasan's answer did not, then it's most likely because of the data type of the grade_date table. If it's DATETIME, then it stores the time element of the date and that makes searching on that field bothersome.

If you don't need the time element, try changing the data type of the column to DATE. It should use less space and make searching easier.

EDIT: Wait, I just read that question again... you don't want records where the date MATCHES the query, you want something slightly trickier. Let me think on this one...

Barry Colebank Jr
  • 1,939
  • 2
  • 16
  • 16
0

Thanks for the replay everyone, I think I found the solution, move subquery to WHERE clause and use MIN function at date diff. Here is the query :

SELECT p.id, p.name, pg.grade, pg.grade_date
FROM person AS p
LEFT JOIN person_grade AS pg ON p.id = pg.person_id
WHERE DATEDIFF ( '2012-02-02', pg.grade_date ) = 
  ( SELECT MIN ( DATEDIFF ( '2012-02-02', spg.grade_date ) )
  FROM person AS sp
  LEFT JOIN person_grade AS spg ON sp.id = spg.person_id
  WHERE DATEDIFF ( '2012-02-02', spg.grade_date ) > 0 AND sp.id = p.id )

Result:

| id |  name   | grade  | grade_date |
|  1 | person1 | grade2 | 2012-01-01 |
|  2 | person2 | grade3 | 2010-05-05 |

Seems reference to outer table doesn't work under FROM clause, but work elsewhere (at least at MySQL).

Thanks for the hint from other question: Retrieve maximal / minimal record.

Community
  • 1
  • 1
mht
  • 1