CODEWITHSUNDEEP
mysqlsql
Barno
Barno

Reputation: 3331

Merge two tables and OrderBy count

I would like to merge the two results of a query to get a sorted table with order by count

I have two Tables The first is: like_spotted Query is 

SELECT l.id as like_id, l.spotted_id as spotted_id,count(l.spotted_id) as numero_likes, 
MAX(l.createdAt) as created
FROM sn_like_spotted l
LEFT JOIN prof_foto f
ON f.id = l.spotted_id
LEFT JOIN sn_profilo p
ON f.profilo_id = p.id
WHERE p.id = 3 
GROUP BY l.spotted_id
ORDER BY numero_likes DESC, created DESC
LIMIT 0,5

Results:

like_id spotted_id  numero_likes    created
    31  223              6          2013-11-21 16:31:20
    11  175              5          2013-11-21 17:00:35
    23  217              2          2013-11-15 22:52:41

The second is: like_answer Query is 

SELECT l.id as like_id, l.risposta_id as risposta_id,count(l.risposta_id) as numero_likes, 
MAX(l.createdAt) as created
FROM sn_like_risposta l
LEFT JOIN sn_risposte r
ON r.id = l.risposta_id
LEFT JOIN sn_profilo p
ON r.profilo_id = p.id
WHERE p.id = 3
GROUP BY l.risposta_id
ORDER BY numero_likes DESC, created DESC
LIMIT 0,5

like_id         risposta_id      numero_likes        created
18                  94                4           2013-11-21 17:00:35
10                  93                2           2013-11-20 13:30:53
7                   85                1           2013-11-14 12:16:11
6                   84                1           2013-11-14 12:15:38

I would like "mixed result" whith order by "numero_likes" DESC Can i do this ?

Upvotes: 0

Views: 51

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Yes you can do this by using UNION ALL

(SELECT l.id as like_id, l.spotted_id as spotted_id,null AS risposta_id , count(l.spotted_id) as numero_likes, 
MAX(l.createdAt) as created
FROM sn_like_spotted l
LEFT JOIN prof_foto f
ON f.id = l.spotted_id
LEFT JOIN sn_profilo p
ON f.profilo_id = p.id
WHERE p.id = 3 
GROUP BY l.spotted_id
LIMIT 0,5)

UNION ALL
(SELECT l.id as like_id, null AS spotted_id,l.risposta_id as risposta_id,count(l.risposta_id) as numero_likes, 
MAX(l.createdAt) as created
FROM sn_like_risposta l
LEFT JOIN sn_risposte r
ON r.id = l.risposta_id
LEFT JOIN sn_profilo p
ON r.profilo_id = p.id
WHERE p.id = 3
GROUP BY l.risposta_id
LIMIT 0,5)
ORDER BY numero_likes DESC, created DESC

UNION

Upvotes: 1

Related Questions