CODEWITHSUNDEEP
phpmysqlsql
Sev
Sev

Reputation: 95

Combining these three SQL Queries

I have developed these three queries, found below, that find the most views, most comments, and most likes that each Article has (an article is defined by both the Content ID and Format). I undersand that Target ID != ContentID and that Format != TargetClass, but I treat them as they are the same thing.

I need to use these three queries to output the top 3 articles that hold the most comments, likes and views, in priority of that order. I'm not exceptionally talented at SQL, so could someone please provide a solution and a mild explanation? If the question needs to be rephrased, please make it clear and I will do so. Cheers.

MOST VIEWS (In descending order; with most views being at the top)

SELECT ContentID, Format
FROM Content
GROUP BY ContentID, Format
ORDER BY COUNT(View) DESC

MOST COMMENTS (In descending order; with most comments being at the top)

SELECT TargetID, TargetClass
FROM Comments
GROUP BY TargetID, TargetClass
ORDER BY COUNT(TargetID) DESC

MOST LIKES (In descending order; with most likes being at the top)

SELECT ContentID, Format
FROM Likes
GROUP BY ContentID, Format
ORDER BY COUNT(ContentID) DESC

EXAMPLE DATA AND OUTPUT (As requested) TABLE 1:

ContentID| Format  |View|
---------|---------|----|
    1    |Paperback|1700|
---------|---------|----|
    1    |  Ebook  |1500|
---------|---------|----|
    2    |Paperback|1500|
-------------------------

TABLE 2:

CommentID|TargetID |TargetClass|
---------|---------|-----------|
    1    |    1    |   Ebook   |
---------|---------|-----------|
    2    |    2    | Paperback |
---------|---------|-----------|
    3    |    1    |   Ebook   |
--------------------------------

TABLE 3:

  LikeID | ContentID| Format  |
---------|---------|---------|
    1    |    1    |Ebook    |
---------|---------|---------|
    2    |    2    |Paperback|
---------|---------|---------|
    3    |    2    |Paperback|
------------------------------

DESIRED SOLUTION:

In table 1 contentId = 1 and format = paperback have the most views, BUT, views are weigh less than likes when decided the popularity of an article. In table 3, ContentId=2 and format=paperback, have more likes than either of the other two articles (the one absent from the table has no likes at all). Comments are weighted most heavily however, and in table 2, targetid=1, format=ebook has more comments than either of the other two articles.

This means that targetid=1 and format=ebook is the most popular book. HOWEVER, when two have the same number of comments, they fall back on the likes to determine the most popular article. However, if, once again, they have the same number of likes, it falls back on views to determine the most popular article.

The output required is a list as follows:

ContentID | Format
1         | ebook
2         | paperback
1         | paperback

In order of their "popularity".

Upvotes: 0

Views: 91

Answers (2)

Dahley
Dahley

Reputation: 71

I think this is the correct solution:

SELECT V1.ContentID, V1.Format
FROM 
        ((SELECT ContentID, Format ,Sum(View) AS CountView
        FROM Content
        GROUP BY ContentID, Format) V1) LEFT OUTER JOIN 

        (
          SELECT V2.ContentID, V2.Format ,CountComments,CountLikes
          FROM
          ((SELECT TargetID AS ContentID, TargetClass AS Format,Count(TargetID) AS CountComments
          FROM Comments
          GROUP BY TargetID, TargetClass) V2) LEFT OUTER JOIN 

          ((SELECT ContentID, Format ,Count(ContentID) AS CountLikes
          FROM Likes
          GROUP BY ContentID, Format) V3)

          ON(V3.ContentID=V2.ContentID AND V3.Format =V2.Format)
        ) V12
        ON(V1.ContentID=V12.ContentID AND V1.Format =V12.Format)
ORDER BY CountComments DESC,CountLikes DESC ,CountView DESC
LIMIT 3;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you can do this with union or union all. Perhaps the following does what you want:

(SELECT TargetID as ContentId, TargetClass
 FROM Comments
 GROUP BY TargetID, TargetClass
 ORDER COUNT(*) DESC
 LIMIT 1
)
UNION ALL
(SELECT ContentID, Format
 FROM CONTENT
 GROUP BY ContentID, Format
 ORDER BY COUNT(*) DESC
 LIMIT 1
)
UNION ALL
(SELECT ContentID, Format
 FROM Likes
 GROUP BY ContentID, Format
 ORDER BY COUNT(*) DESC
 LIMIT 1
)

Upvotes: 1

Related Questions