CODEWITHSUNDEEP
phpmysql
Prst
Prst

Reputation: 47

SELECT and ORDER BY that column using COUNT from another table

My scenario is this:

I have 3 tables with structures as follows:

articles: article_id, more data;
tags: tag_id, tag_name, more data;
article_tags: article_tag_id, tag_id, article_id

Each article can have multiple tags. I want to retrieve the tags of an article (article_id is provided) ordered by the total number of times that tag was used.

For Eg:

Article1 has tags 'tag4', 'tag3', 'tag2'
Article2 has tags 'tag4', 'tag3'
Article3 has tags 'tag4'
Article4 has tags 'tag4', 'tag3', 'tag2', 'tag1'

So when I am looking for the tags of article4, it should order it so:

1. tag4 (4 occurrences)
2. tag3 (3 occurrences)
3. tag2 (2 occurrences)
4. tag1 (1 occurrence)

Is this possible with one MySql query? Currently I am just retrieving all the tags of that article, then another array with the list of tags ordered by occurrences, then just manually sorting the former array using the later.

Upvotes: 0

Views: 143

Answers (3)

Falci
Falci

Reputation: 1873

I think that's

SELECT t.tag_name, COUNT(at.*) as total
  FROM tags t
  JOIN article_tags at ON at.tag_id=t.tag_id
 WHERE t.tag_id IN (SELECT tag_id FROM article_tags at2 WHERE at2.article_id = ?)
 GROUP BY t.tag_name
 ORDER BY total DESC

UPDATE: Added the article ID in where clause

Upvotes: 1

Michael
Michael

Reputation: 211

the following sql statement should give you the result you want. replace 777 with a specific article id

select t.tag_name, count(at.article_tag_id) as [cnt]
from article_tags at
join tags t on (t.tag_id = at.tag_id)
join articles a on (a.article_id = at.article_id)
where a.article_id = 777
group by tag_name
order by cnt desc

Upvotes: 0

John Woo
John Woo

Reputation: 263693

I prefer to use LEFT JOIN in this case because there are probably that a tag may be unused. 

SELECT  a.tag_name
FROM    tags a
        LEFT JOIN article_tags b
            ON a.tag_id = b.tag_id
GROUP   BY a.tag_name
ORDER   BY COUNT(b.tag_id) DESC

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 2

Related Questions