Most used tags of author - mysql normalised database

I can't get this query to work. I tried this, but doesn't work:

SELECT tags.*
FROM blog_tags AS tags 
LEFT JOIN blog_items AS items ON (items.authorid = 4) 
LEFT OUTER JOIN blog_items_tags AS itemstags ON (itemstags.itemid = items.id) 
GROUP BY tags.id

What I wa开发者_Python百科nt is to get all the tags an author has used in articles.

For the idea: The databases are:

• blog_tags (id, title)
• blog_items (just articles with field id, authorid, etc...)
• blog_items_tags (itemid, tagid).

What's the correct statement? (I'm not very familiar with mysql)

I always use subqueries in cases like this. You can use joins, too, but I've never been as comfortable writing those (and I think they evaluate to the same DB lookup anyway - but correct me if I'm wrong!), so I'd write something like this:

SELECT tags.* FROM tags WHERE id IN (
    SELECT tagid FROM blog_items_tags WHERE itemid IN (
        SELECT id FROM blog_items WHERE authorid = 4
    )
);

It's a bit messier than a join, but It makes more sense to me when I go back and read it - hope this helps!

Edit: If you want the number of times each tag was used, you can add a COUNT and a GROUP BY to the query:

SELECT tags.*, COUNT(*) FROM tags WHERE id IN (
    SELECT tagid FROM blog_items_tags WHERE itemid IN (
        SELECT id FROM blog_items WHERE authorid = 4
    )
) GROUP BY tags.title;

You are missing a join condition (your blog_tags table is not used anywhere). In addition, you need an INNER JOIN as you want only the tags that were used, not all tags, and related info.

Here is corrected query:

SELECT tags.*
FROM blog_tags AS tags 
INNER JOIN blog_items_tags AS itemstags ON (itemstags.tagid = tags.id) 
INNER JOIN blog_items AS items ON items.authorid = 4 AND items.id = itemtags.itemid 
GROUP BY tags.id

Troubleshoot by running simpler versions of this query, then building up. Start with

SELECT tagid FROM blog_tags

Then work up.

In particular, try removing AS. This flavor of SQL may require it to be absent:

SELECT tagid FROM blog_tags tags