MySQL query help
- Thread starter TheRealAndyCook
- Start date
nvm fixed the issue.
Code:
SELECT post.*,post.type as type, p1.username AS username,
p1.id AS userid, COUNT(test2.id) AS replies, commentsixty, p2.username AS reply,
p2.id AS replyid, max(test2.id) as lastid, test2.time2,
CASE
WHEN test2.time2 IS NULL THEN post.time
WHEN test2.time2 IS NOT NULL THEN test2.time2
ELSE '1'
END AS time3,
CASE
WHEN photo.url IS NULL THEN '/imgs/nophoto.png'
WHEN photo.url IS NOT NULL THEN photo.url
ELSE '1'
END as url
FROM post
LEFT JOIN user AS p1 ON post.uid = p1.id
LEFT JOIN photo ON p1.photo = photo.id AND p1.id = photo.uid
LEFT JOIN (Select id, pid, uid, time as time2 from comment
ORDER BY id DESC) as test2
ON post.id = test2.pid
LEFT JOIN user AS p2 ON test2.uid = p2.id
LEFT JOIN (Select pid,id,count(id) as commentsixty from comment
WHERE comment.time between CAST(DATE_SUB(now(), interval 60 minute) as DATETIME) and cast(now() as DATETIME)
[COLOR="Red"]GROUP BY pid [/COLOR]
)as test666
ON post.id = test666.pid
Where post.cid = 1 and type > '0'
GROUP BY post.id
ORDER BY test666.id DESC