mardi 5 mai 2015

Any way to combine these 3 SQL Queries Together?

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