I have 3 seperate queries at the moment that I'm trying to combine together so it is more efficient.
The reason i'm putting them together is so that I can sort all of the results by submitdate, as of right now they are sorted by submitdate but are seperated by each query.
Would appreciate any help on the matter, thanks a billion guys.
First is
Query that gets all posts that I have commented on that have new comments
SELECT DISTINCT p.*,c.submitdate as MostRecentSubmitDate
FROM posts p
INNER JOIN comments c
ON c.postid = p.id
WHERE c.submitdate > (
SELECT MAX(c2.submitdate)
FROM comments c2
WHERE c2.postid = c.postid
AND c2.deviceID = "$DeviceID"
)
Second is
Query that gets most recent replies on my posts.
SELECT p.PostTitle,p.id AS PostID,c1.id AS CommentID, c1.comment, q.LatestCommentDate, c1.deviceID
FROM (SELECT c.postid, MAX(c.SubmitDate) AS LatestCommentDate
FROM comments c GROUP BY c.postid) q
INNER JOIN posts p ON q.postid = p.id and "$DeviceID" = p.deviceiD
INNER JOIN comments c1 ON q.LatestCommentDate = c1.submitDate
Thirdly is
Query that gets the amount of votes on each of my posts
SELECT * FROM posts
WHERE DEVICEID = "$DeviceID"
AND PostVotes > 0
ORDER BY SUBMITDATE
Aucun commentaire:
Enregistrer un commentaire