MySQL Query with some type of join? Not sure.

Question

If anyone could recommend a good book for learning mySQL as well, that would be great :).

I have two tables, tags, codes_tags

CREATE TABLE `tags` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=190 DEFAULT CHARSET=utf8


CREATE TABLE `codes_tags` (
 `code_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 KEY `sourcecode_id` (`code_id`),
 KEY `tag_id` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

What I am trying to do is select the name from 'tags', and how many of that tag_id there are in 'codes_tags', and order them by that count. If there is no records in codes_tags for that tag_id, 'count' should be equal to 0 or NULL (preferably 0).

This is the closest I have come so far:

SELECT tags.name, COUNT( codes_tags.tag_id ) AS count
FROM tags
LEFT JOIN codes_tags ON tags.id = codes_tags.tag_id
GROUP BY tag_id
ORDER BY count DESC
LIMIT 0 , 30

It seems to do what I am wanting, however it is only returning four rows when it should return 30.

What am I doing wrong here? Thanks.

Answer 1

I think if you change your COUNT(codes_tags.tag_id) to COUNT(*) in the SELECT, that NULLs will also be included. (If it's nulls or 0 counts that you're missing. Otherwise, the query looks fine).

EDIT: On second thought, I missed the LEFT JOIN. That would mean you want all of the tags even if they're not related to something in the codes_tags table. Is that what you want?

I would probably do something like the following:

SELECT tags.name, COUNT(*) AS count
FROM tags
INNER JOIN codes_tags ON tags.id = codes_tags.tag_id
GROUP BY tags.id
ORDER BY count(*) DESC

It can be inferred from the items not in the list which tags are not also included in codes_tags. However, if you wanted to explicitly do that as well:

SELECT tags.name, COUNT(*) AS count
FROM tags
INNER JOIN codes_tags ON tags.id = codes_tags.tag_id
GROUP BY tags.id
UNION
SELECT tags.name, '0'
from tags
where tags.name not in 
   (SELECT tags.name
   FROM tags
   INNER JOIN codes_tags ON tags.id = codes_tags.tag_id)
ORDER BY count(*) DESC

(I don't have access to a SQL box at the moment, so take the queries with a grain of salt; they're untested.)

Answer 2

Change the LEFT JOIN to LEFT OUTER JOIN

Answer 3

I've tested this out on MySQL with some dummy data and the query appears to return more than 4 rows for me. I ran your create table statements and then populated them with the following statements:

insert into tags (name) values ('java'), ('mysql'), ('php'), ('ruby'), ('.net'), ('python');
insert into codes_tags (code_id, tag_id) values (1,194), (2,194), (3,194), (1,191), (2,191), (3,191), (4,191), (5,191), (1,192), (1,195), (1,193);

When I run your query on that data, it returns 6 rows. In order to help further debug this, can you post the results of the following 2 queries:

select count(*) from tags;
select * from tags limit 10;

Also, in order to make sure you have proper data integrity, can you add the following foreign key and see if it succeeds?

alter table codes_tags add foreign key codes_tags_tag_id_key(tag_id) references tags(id);