sirnutty1
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.
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.
Last Comment
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
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
try removing the "as attendancecount" part and see if it works and instead of Count(ATT.*) try Count(ATT.ID)
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.
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Many thanks for all your help brad2575, very much appreciated.
PHP
PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.
125K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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.