Banging my head on sql again. :P
|
| Author | Content |
|---|---|
| techiem2 Feb 06, 2009 3:12 PM EDT |
We are trying to build a new attendance system to integrate into moodle and I'm trying to figure out the basic of grabbing the data, etc. first. I have the following query and output code http://pastebin.com/f1061e8d4 Basically, it is supposed to grab the user info from the moodle users table and the info from the attendance table and then print out everyone that has an attendance status of A (absent). So I have it check the id against the student_id in the attendance table so we only get results of students that actually have attendance. However, whenever I run the query, sql is only returning one result. I'm assuming it's a syntax or logic error on my part. If I run the test without the id matching, it returns the first user in the user table along with the info from the first user in the attendance table as though it were a single user. Any ideas on where my logic and sql are messed up? Thanks as always guys/gals. |
| theboomboomcars Feb 06, 2009 5:22 PM EDT |
A couple of questions. 1. Is the attendance something that the input selects or do the inputers put in what ever they fell best represents the situation? 2. If you take out the where statement does it return all records? Sorry I can't be more help but my SQL skills are a bit rusty. |
| Sander_Marechal Feb 06, 2009 8:32 PM EDT |
I think the bug is: COUNT(t2.status) It counts all the rows, but you're not telling it *what* to count, so it counts (and aggregates) them all. Remove the COUNT or add a "GROUP BY t2.status" clause. |
| techiem2 Feb 06, 2009 9:30 PM EDT |
hmm. I'll check that stuff when I reboot the laptop this weekend (it's currently in Winders for some silly homework and a test...blegh). |
| dinotrac Feb 06, 2009 11:41 PM EDT |
Sander -- You are correct, though, without the actual tables, the precise nature of the bug is a little tricky to tell. If we were to take that sql out of the join context, mysql would barf all over you for including a count() in the same statement with non-aggregated fields and no group by. I'm a little surprised that it didn't barf on the join, but that may be an artifact of the data you've got. Don't know. |
| theboomboomcars Feb 07, 2009 12:19 AM EDT |
Quoting:Basically, it is supposed to grab the user info from the moodle users table and the info from the attendance table and then print out everyone that has an attendance status of A (absent). So I have it check the id against the student_id in the attendance table so we only get results of students that actually have attendance. However, whenever I run the query, sql is only returning one result. I'm assuming it's a syntax or logic error on my part. From your description you do not need the count. If you want a count of how many absences each student has you will need to group it by t1.id, t1.firstname, and t1.lastname. From what you are describing grouping by the t2.status will return the same result as what you have (one line displaying the first name in the list and the total of everybody.) To make you code a little cleaner if you don't need the ID displayed twice you can take out the t1.id or t2.student_id from your select and echo statements. My wife tested the code you provided changing the table references to tables from the database she set up for our movie collection. |
| krisum Feb 07, 2009 7:28 AM EDT |
Yes, it looks like: SELECT t1.id, t1.firstname, t1.lastname, COUNT(*) from mdl_user AS t1, attendance_records AS t2 WHERE t1.id = t2.student_id and t2.status='A'" GROUP BY t1.id is what you need, assuming student ID to be unique. If there can be duplicate entries for a student in a discipline in attendance_records and say "discipline" is the column that gives name of the discipline where attendance was recorded then: SELECT t1.id, t1.firstname, t1.lastname, COUNT(*) from mdl_user AS t1, (SELECT DISTINCT student_id, status, discipline FROM attendance_records) AS t2 WHERE t1.id = t2.student_id and t2.status='A'" GROUP BY t1.id |
| techiem2 Feb 11, 2009 8:21 PM EDT |
Thanks guys! It was the count breaking the query. |
Posting in this forum is limited to members of the group: [Editors, MEMBERS, SITEADMINS.]
Becoming a member of LXer is easy and free. Join Us!