MySQL GROUP BY *RESOLVED*

**dj4uk** · Jul 13th, 2005, 04:50 AM

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

**dee-u** · Jul 13th, 2005, 05:04 AM

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

**dj4uk** · Jul 13th, 2005, 05:12 AM

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

**dj4uk** · Jul 13th, 2005, 05:14 AM

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

**dee-u** · Jul 13th, 2005, 05:16 AM

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'

**dee-u** · Jul 13th, 2005, 05:20 AM

What happened? Why is it resolved already?

Ooooppsss.... I missed Post #4.

Thread: MySQL GROUP BY RESOLVED

Thread Tools

Display