I want to select the min value of a id from other table
here my table1
| id | grades |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 5 |
my table2
| id | name |
|---|---|
| 1 | andy |
| 2 | lucy |
| 3 | kevin |
I tried this
select table2.id, name, min(table1.grades) as grade from table1, table2
but it only shows 1 record
what I expected
| id | name | grade |
|---|---|---|
| 1 | andy | 1 |
| 2 | lucy | 2 |
| 3 | kevin | 0(or null?) |
thanks
CodePudding user response：
SELECT id, table2.name, MIN(table1.grades) grade
FROM table1
JOIN table2 USING (id)
GROUP BY 1,2;