MySQL GROUP BY *RESOLVED*

I can't see why the following SELECT statement won't work - any ideas?

Code:

---

SELECT                                s.subject_id, s.subject_name
FROM                                tbl_subjects AS s
INNER JOIN                        tbl_lessons AS l
ON                                s.subject_id = l.subject_id
WHERE                                l.online = 'True'
GROUP BY                        s.subject_id, s.subject_name
ORDER BY                        s.subject_name ASC

---

The error I get is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-3.23.55-nt]You have an error in your SQL syntax near 'BY s.subject_name ASC' at line 1

/html/newLogin.asp, line 23

Stumped again... :(

DJ

How about this?

Code:

---

SELECT              s.subject_id, s.subject_name
FROM                tbl_subjects AS s
INNER JOIN          tbl_lessons AS l
ON                  s.subject_id = l.subject_id
WHERE              l.online = 'True'
ORDER BY            s.subject_name ASC
GROUP BY            s.subject_id, s.subject_name

---

The sql below works for me...

Code:

---

SELECT                VS.VS_Vessel, SS.SS_CarrierCode
FROM                SS
INNER JOIN        VS
ON                SS.SS_RefNum = VS.VS_RefNum
GROUP BY        VS.VS_Vessel, SS.SS_CarrierCode
ORDER BY        SS.SS_CarrierC

---

Now I get:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-3.23.55-nt]You have an error in your SQL syntax near 'GROUP BY s.subject_id, s.subject_name ' at line 1

/html/newLogin.asp, line 23

I'm pretty sure the GROUP BY has to come first.

DJ

Got it working:

Code:

---

SELECT                                tbl_subjects.subject_id, tbl_subjects.subject_name
FROM                                tbl_subjects
INNER JOIN                        tbl_lessons
ON                                tbl_subjects.subject_id = tbl_lessons.subject_id
WHERE                                tbl_lessons.online = 'True'
GROUP BY                        tbl_subjects.subject_id, tbl_subjects.subject_name
ORDER BY                        tbl_subjects.subject_name ASC

---

It didn't seem to like the table aliases!

Thanks for the suggestions.

DJ

This works too....

Code:

---

SELECT VS.VS_Vessel, SS.SS_CarrierCode
FROM SS INNER JOIN VS ON SS.SS_RefNum = VS.VS_RefNum
GROUP BY VS.VS_Vessel, SS.SS_CarrierCode
ORDER BY  SS.SS_CarrierCode ASC;

---

Why don't you try removing this line...

Code:

---

WHERE                                l.online = 'True'

---

What happened? Why is it resolved already? :confused:

Ooooppsss.... I missed Post #4. :D