Avatar of sirnutty1
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.
Web DevelopmentPHPMySQL Server

Avatar of undefined
Last Comment
sirnutty1

8/22/2022 - Mon
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.
ASKER
sirnutty1

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)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
sirnutty1

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
sirnutty1

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