CODEWITHSUNDEEP
mysqljoin
laphiloctete
laphiloctete

Reputation: 476

Simple MYSQL Query Question

    SELECT t.thread_id, t.subject, u.username,
    COUNT(p.post_id) - 1 AS responses, 
    MAX(DATE_FORMAT(p.posted_on, '%e-%b-%y %l:%i %p')) AS last,
    MIN(DATE_FORMAT(p.posted_on, '%e-%b-%y %l:%i %p')) AS first
    FROM threads AS t
    INNER JOIN posts AS p USING (thread_id)
    INNER JOIN users AS u ON t.user_id = u.user_id
    WHERE t.cat_id = 1
    GROUP BY (p.thread_id) ORDER BY last DESC

I thought I did this right, however my query is only returning one row, I have 4 rows where t.cat_id = 1. Any ideas?

EDIT** SAMPLE DATA http://i56.tinypic.com/f1e449.png

Right now im only getting just one result:

1  THREAD1  USER1  8  17-Apr-11 6:22 AM  17-Apr-11 3:58 AM

I was expecting to get something like:

1  THREAD1  USER1  8  17-Apr-11 6:22 AM  17-Apr-11 3:58 AM
2  THREAD2  USER1  8  17-Apr-11 6:22 AM  17-Apr-11 3:58 AM
3  THREAD3  USER1  8  17-Apr-11 6:22 AM  17-Apr-11 3:58 AM

Upvotes: 2

Views: 96

Answers (2)

Johan
Johan

Reputation: 76537

You have selected t.thread_id, yet you group by p.thread_id.

Change the group by into

GROUP BY (t.thread_id) ORDER BY last DESC  
          ^

Upvotes: 0

Oded
Oded

Reputation: 498934

Chances are that there are no corresponding rows in one of the join tables:

INNER JOIN posts AS p USING (thread_id)
INNER JOIN users AS u ON t.user_id = u.user_id

Another possibility (not mutually exclusive of the first) is that the grouping clause collapses the rows.

Upvotes: 1

Related Questions