CODEWITHSUNDEEP
mysqlsql
santa
santa

Reputation: 12512

Latest results from db

I need display results from two joined tables only for the latest project. I have the following query:

  SELECT project.id,
         project.created,
         COUNT(DISTINCT events.user_id) AS cnt
    FROM project
    JOIN events ON (events.project_id = project.id)
   WHERE project.creator = $creatorID
     AND events.user_id != $creatorID
ORDER BY project.created DESC
   LIMIT 1

For some reason I keep on getting the first project... What am I missing here?

Upvotes: 0

Views: 45

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270617

You're definitely on the right track. It seems you're missing a GROUP BY clause for your aggregate COUNT(). Try this:

SELECT
  project.id,
  project.created,
  count(DISTINCT events.user_id) AS cnt
FROM project
  JOIN events ON (events.project_id = project.id)
WHERE
  project.creator = $creatorID
  AND events.user_id != $creatorID
GROUP BY project.id, project.created
ORDER BY project.created DESC,
LIMIT 1

Upvotes: 2

Related Questions