I have a table called Request.
Other tables are linked to the Request table through a request id.
There is a TwitterTweet table and a FacebookPost table.
So a single request can have 50 TwitterTweets and/or 20 FacebookPosts or any amount of Tweets/Posts
We can add them together for a total count of 70.
I'm trying to create a query that could tell me what is the request with the highest total count.
I know this is wrong: (I attempted to just order them by the counts within the TwitterTweet, but it would not let me do an OUTER JOIN which I thought would bring back the Count.count column. It forced me to do a Left Join for it to compile. My Logic was to do a join so that the results were calculated for each row by the requestid)
SELECT r1.`id` AS requestid, r1 . *
FROM `Request` AS r1
LEFT JOIN
(SELECT COUNT( * ) AS count, rid
FROM
((SELECT `TwitterTweet`.`id` AS `smid` , `TwitterTweet`.`requestid` AS rid
FROM `TwitterTweet`
WHERE `TwitterTweet`.`requestid` = requestid
AND `TwitterTweet`.`active` =1) AS talias
)) AS Count ON ( Count.rid = requestid )
ORDER BY Count.count
*When I tried to add in the Facebook side it would not compile any more (The concept is that the results are added from TwitterTweet with the results from FacebookPost that are attached to the specific requestid which would give us a count. The entire result set should be ordered by that count)
SELECT r1.`id` AS requestid, r1 . *
FROM `Request` AS r1
LEFT JOIN
(SELECT COUNT( * ) AS count, rid
FROM
((SELECT `TwitterTweet`.`id` AS `smid` , `TwitterTweet`.`requestid` AS rid
FROM `TwitterTweet`
WHERE `TwitterTweet`.`requestid` = requestid
AND `TwitterTweet`.`active` =1 ) AS talias
UNION All
(SELECT `FacebookPost`.`id` AS `smid`, `FacebookPost`.`requestid` AS rid
FROM `FacebookPost`
WHERE `FacebookPost`.`requestid` = requestid
AND `FacebookPost`.`active` = 1) as falias
)) AS Count ON ( Count.rid = requestid )
ORDER BY Count.count
I updated the Query with an attempt to add an alias:
SELECT rid, SUM(count) total_count
FROM
(
(SELECT COUNT(*) AS count, r.rid
FROM request r
JOIN TwitterTweet tt
ON r.id = tt.requestid
WHERE tt.active = 1
GROUP BY r.rid) AS twitter
UNION ALL
(SELECT COUNT(*) AS count, r.rid
FROM request r
JOIN FacebookPost fp
ON r.id = fp.requestid
WHERE fp.active = 1
GROUP BY r.rid ) AS fbook
)
GROUP BY rid
ORDER BY SUM(count) DESC
I made another adjustment to give the middle subquery an alias, but now I only get one row returned with a zero in the rid column and 5686 in the total_count column...the 5686 might be all of the results.
SELECT counts.rid, SUM(count) total_count
FROM
(
SELECT COUNT(*) AS count, r.requestid AS rid
FROM request r
JOIN TwitterTweet tt
ON r.id = tt.requestid
WHERE tt.active = 1
GROUP BY r.requestid
UNION ALL
SELECT COUNT(*) AS count, r.requestid AS rid
FROM request r
JOIN FacebookPost fp
ON r.id = fp.requestid
WHERE fp.active = 1
GROUP BY r.requestid
) AS counts
GROUP BY counts.rid
ORDER BY SUM(count) DESC
Got it!!! Thanks for your help guys, I had to remove those joins on the request:
SELECT counts.rid, SUM(count) total_count
FROM
(
SELECT COUNT(*) AS count, tt.requestid AS rid
FROM TwitterTweet tt
WHERE tt.active = 1
GROUP BY tt.requestid
UNION ALL
SELECT COUNT(*) AS count, fp.requestid AS rid
FROM FacebookPost fp
WHERE fp.active = 1
GROUP BY fp.requestid
) AS counts
GROUP BY counts.rid
ORDER BY SUM(count) DESC
SELECT id, SUM(count) total_count
FROM
(
SELECT COUNT(*) AS count, r.id
FROM request r
JOIN TwitterTweet tt
ON r.id = tt.requestid
WHERE tt.active = 1
GROUP BY r.id
UNION ALL
SELECT COUNT(*) AS count, r.id
FROM request r
JOIN FacebookPost fp
ON r.id = fp.requestid
WHERE fp.active = 1
GROUP BY r.id
) sub
GROUP BY id
ORDER BY SUM(count) DESC
;