CODEWITHSUNDEEP
mysqlsqlcounttags
XTRUST.ORG
XTRUST.ORG

Reputation: 3402

Mysql | Count tags

I have two tables (tags and tag map):

Tags:

id   text
1    tag1
2    tag2
3    tag3

Tag map:

tag_id  question_id
1       1
1       2
1       3
1       4
2       5
3       6

I would like to get results like in the table below:

id   text   count
1    tag1   4  
2    tag2   1
3    tag3   1

My query:

SELECT 
    t.id, 
    t.text
FROM
    `#__tags` AS t

How can I modify my query to return count. Thanks!

Upvotes: 0

Views: 921

Answers (3)

Sadikhasan
Sadikhasan

Reputation: 18598

SELECT t1.id,
       t1.text,
       count(t2.tag_id) AS `count`
FROM tag t1
LEFT JOIN tag_map t2 ON t1.id=t2.tag_id
GROUP BY t1.id;

Upvotes: 0

Suhel Meman
Suhel Meman

Reputation: 3852

Use below query: 

SELECT t1.id,
       t1.text,
       count(t2.question_id) AS COUNT
FROM Table1 t1
LEFT JOIN Table2 t2 ON (t1.id=t2.tag_id)
GROUP BY t1.id;

SQL Fiddle Demo

Upvotes: 2

Allmighty
Allmighty

Reputation: 1519

You can achieve this by grouping the individual ID's of the map table, and then counting the number of rows in each group. Edit: oh, and offcourse you need to filter out the rows which don't belong in the cartesian product that the joining of 2 tables generates by including a where-clause.

SELECT 
    t.id, 
    t.text,
    COUNT(tm.tag_id) AS count
FROM
    `#__tags` AS t, 
    `tag_map` AS tm
WHERE t.id = tm.tag_id
GROUP BY
    tm.tag_id

Upvotes: 0

Related Questions