I need help with making a query.
My tables are: (Simplified)
case:
id
violatorID
number
violator:
id
name
trial:
id
caseID
date
A Case can have Multiple trials with different dates.
I need a query that gets returns the following information
caseID, caseNumber, violatorName, and the FIRST trial date.
So i need distinct cases, but it should return the earliest trial date. If there's on in March and one in July, it should return the one in March.
MY ATTEMPT:
Expand|Select|Wrap|Line Numbers
- SELECT c.id as NUM, c.caseNumber, v.firstName, v.lastName, t.courtDate
- FROM `case` as c, violator as v, trial as t
- WHERE c.violatorID = v.id AND t.CaseID = c.id
- GROUP BY c.id;
Then i tried this:
Expand|Select|Wrap|Line Numbers
- SELECT c.id as NUM, c.caseNumber, v.firstName, v.lastName, t.courtDate
- FROM `case` as c, violator as v, trial as t
- WHERE c.violatorID = v.id AND t.CaseID = c.id
- ORDER BY t.courtDate
- GROUP BY c.id;
- //and got...
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY c.id' at line 1
any tips much appreciate it.
for the record, I know violator should have caseID, instead of vice versa.