SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: COUNT() Problem

  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question COUNT() Problem

    Background:
    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.

    Problem:
    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.

    Example:
    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.

    Thanks,
    Michael

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •