hi,
could anyone take a look at following problem?


i have created two tables

mysql> show create table students;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`Studid` int(11) default NULL,
`firstname` varchar(20) default NULL,
`lastname` varchar(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table grade;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| grade | CREATE TABLE `grade` (
`gradeid` int(11) default NULL,
`Studid` int(11) default NULL,
`grade` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

then i loaded data into tabes from following files

csxia-desktop:/data/learning/mysql/load # cat stduent
1 Bill Smith
10 guifen xia
4 chengshun xia
2 Bobby Brown
3 Derek Zoolander
csxia-desktop:/data/learning/mysql/load # cat grade
1 2 61
2 3 98
3 1 87
4 10 90
5 10 81
6 4 78
7 4 90
8 1 50
9 2 23
10 3 100
11 10 90
12 4 40
18 1 58
19 2 82
20 3 68
csxia-desktop:/data/learning/mysql/load #


what i want to do is use query to list all grade for all stduents


query #1: it works fine

mysql> select S.Studid,S.firstname,S.lastname,G.grade from students S inner join grade G on S.Studid = G.Studid order by S.Studid,G.grade DESC;+--------+-----------+-----------+-------+
| Studid | firstname | lastname | grade |
+--------+-----------+-----------+-------+
| 1 | Bill | Smith | 87 |
| 1 | Bill | Smith | 58 |
| 1 | Bill | Smith | 50 |
| 2 | Bobby | Brown | 82 |
| 2 | Bobby | Brown | 61 |
| 2 | Bobby | Brown | 23 |
| 3 | Derek | Zoolander | 100 |
| 3 | Derek | Zoolander | 98 |
| 3 | Derek | Zoolander | 68 |
| 4 | chengshun | xia | 90 |
| 4 | chengshun | xia | 78 |
| 4 | chengshun | xia | 40 |
| 10 | guifen | xia | 90 |
| 10 | guifen | xia | 90 |
| 10 | guifen | xia | 81 |
+--------+-----------+-----------+-------+
15 rows in set (0.00 sec)

mysql>

and i use query #2 while list only one rows for each stduents

mysql> select S.Studid,S.firstname,S.lastname,G.grade from students S inner join grade G on S.Studid = G.Studid group by S.Studid order by S.Studid,G.grade DESC;
+--------+-----------+-----------+-------+
| Studid | firstname | lastname | grade |
+--------+-----------+-----------+-------+
| 1 | Bill | Smith | 87 |
| 2 | Bobby | Brown | 61 |
| 3 | Derek | Zoolander | 98 |
| 4 | chengshun | xia | 78 |
| 10 | guifen | xia | 90 |
+--------+-----------+-----------+-------+
5 rows in set (0.00 sec)

mysql>


could anyone point out what is the wrong points and what is the difference between query 1 and query 2??


thanks

well, the difference should be pretty obvious, query 1 lists all grades for each student, but query 2 lists only one grade per student

as for whether that's wrong or not, that all depends on which grade for each student you wanted to see

since you use a GROUP BY clause on the student but don't use an aggregate function on the grade, the grade you get for each student is indeterminate

see GROUP BY and HAVING with Hidden Columns

thanks for your quick reply!