asked on

MYSQL - Sub-query, join, something else?

Hi Experts
I have been looking through the answers for my particular issue and it's rather a case of too many options!! Can't quite seperate the wood from the trees! Any help would be greatly appreciated.

I have 2 tables. Table one lists students personal info and table 2 lists indivdual attendances at class for each student.

I want to be able to return a recordset that includes all the student personal info as listed in table 1 as well as an extra column for the total amount of claases attended for each student.

I understand that this is most probably a join or a sub-query but can't figure out what would be best. Many thanks in advance for your help.

brad2575

Select PI.*, Count(ATTt.*) as AttendanceCount
From PersonalInfoTable PI Left Outer Join
AttendanceINfo ATT ON PI.ID = ADD.ID
Group By PI.*

For the group by this must contain all the fields you are selecting above minus the count.

sirnutty1

ASKER

Many thanks for your quick response brad2575

I'm trying this query but keep getting an error (using Dreamweaver).

MySQL Error#: 1064
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 '*) AS slg FROM students s LEFT OUTER JOIN attendance a ON s.id = a.masID Group' at line 1

I have tried various tweaks but it keeps coming back with the same error. Any ideas?

Many thanks

brad2575

try removing the "as attendancecount" part and see if it works and instead of Count(ATT.*) try Count(ATT.ID)

sirnutty1

ASKER

Many thanks brad2575

I am no longer getting an error using the tips you suggested above but the query is very slow. Any suggestions.

SELECT s.id, s.firstName, s.lastname, s.lastGrading, COUNT(a.id) AS slg 
FROM students s 
LEFT OUTER JOIN
attendance a ON s.id = a.masID
Group By s.id

Open in new window

ASKER CERTIFIED SOLUTION

brad2575

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sirnutty1

ASKER

Many thanks for all your help brad2575, very much appreciated.