Help me understand this MySQL code

Question

$query = "SELECT posts.*
    FROM map, posts, tags
    WHERE map.tag_id = tags.id
    AND (tags.name IN ('mysql', 'database'))
    AND map.post_id = posts.id
    GROUP BY posts.id
    HAVING COUNT( posts.id ) = 2";

I don't understand the last row. Can someone please explain it for me? What is the difference if I don't have it there?

Answer 1

The last row says that you leave only those groups having exactly 2 post ids. If you remove it, all groups will be selected.

Answer 2

The "HAVING" predicate acts as a filter, which selects only rows with two (2) of the same post.id.

It looks like the query is looking for rows with duplicate IDs.

HAVING COUNT( posts.id ) > 1

would probably be better for this purpose.

Answer 3

From the docs:

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

So the where clause gets applied, then group by, then having.. Having can apply to values caluclated after your group by has rolled up all your aggregates like SUM or MAX.

http://dev.mysql.com/doc/refman/5.1/en/select.html