Results 1 to 2 of 2
Thread: COUNT() Problem
Mar 7, 2005, 17:46 #1
- Join Date
- Feb 2005
- 0 Post(s)
- 0 Thread(s)
I'm using COUNT(table.field) as variableName with PHP and MySql in one of my queries to count the number of records in one table associated with a record in another table.
Everything works fine with the count until it reaches the last record. When it loops through the records it gives the correct count of associated records until it reaches the last record where begins counting by 2 instead of 1.
Acme Job A: 1 task to do
Acme Job B: 1 task to do
Acme Job C: 2 task to do (this count() should only be 1)
If I have 2 tasks on the last record it reads 4 and so on.
This is my query:
$queryProject = "SELECT *, COUNT(linkprojecttask.taskId) as numTask,
FROM linkuserclient, linkclientproject, linkprojecttask
RIGHT JOIN user, clients
ON user.userId = linkuserclient.userId AND clients.clientId = linkuserclient.clientId
INNER JOIN project, tasks
ON linkclientproject.clientId = clients.clientId AND linkclientproject.projectId = project.projectId
AND linkprojecttask.projectId = project.projectId AND linkprojecttask.taskId = tasks.taskId
WHERE linkuserclient.userId = '$userId' AND tasks.taskActive = 'true' GROUP BY linkprojecttask.projectId";
Any help would be appreciated.
Mar 8, 2005, 05:35 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 52 Post(s)
- 2 Thread(s)
you're apparently getting cross join effects
that's my best guess without understanding your query, which i really didn't want to do, seeing as how it mixes table-list joins with JOIN syntax -- you really should rewrite the joins to use a JOIN syntax in a methodical way
also, you cannot say "SELECT * " at the same time as COUNT()
(well, actually, in mysql you can, but you may get unpredictable results, it even says that in the manual)