[MySQL] JOIN; only one record please!

Question

OK, I have a complicated query from a poorly designed DB... In one query I need to get a count from one database, information from another with a link from another, here goes:

Each blog has a type (news, report etc) and a section Id for a certain part of the site but it also can be linked to multiple computer games and sections)

type ( blog_id, title, body, etc...) // yes I know the type is the name of the blog and not just an id number in the table not my design

blog_link ( blog_id, blog_type, section_id, game_id )

blog_comments (blog_id, blog_type, comment, etc...)

So the query goes a little like this:

SELECT bl.`blog_id`, count(bc.`blog_id`) AS 'comment_count', t.`added`
FROM blog_link bl
JOIN type t ON t.`id` = bl.`blog_id`
JOIN blog_comments bc ON (`item_id` = bl.`blog_id` AND `blog_type` = '[$type]')
WHERE bl.`section_id` = [$section_id] AND bl.`blog_type` = '[$type]'
GROUP BY bl.`blog_id`
ORDER BY `added` DESC
LIMIT 0,20

Now this is fine so long as I do not have multiple games associated with one blog.

Edit: So currently if more than one game is associated the comment_count is multiplied by the amount of games associated... not good.

I have no idea how I could do this... It just isn't working! If I could somehow group by the blog_id before I join it would be gold... anyone got an Idea?

Many thanks in advance

• Dorjan

edit2: I've offered a bounty as this problem surely can be solved!! Come on guys!

Answer 1

Not tested :

SELECT bl.`blog_id`, count(bc.`blog_id`) AS 'comment_count', t.`added`
FROM
(
  SELECT DISTINCT blog_id, blog_type 
  FROM blog_link
  WHERE
   `section_id` = [$section_id] 
   AND `blog_type` = '[$type]'
) bl
INNER JOIN blog_comments bc ON (
    bc.`item_id` = bl.`blog_id` AND bc.`blog_type` = bl.`blog_type`
)
INNER JOIN type t ON t.`id` = bl.`blog_id`
GROUP BY bl.`blog_id`
ORDER BY t.`added` DESC
LIMIT 0,20

Answer 2

I think you need to get the blogs of type "X" first, then do a count of comments for those blogs.

SELECT 
EXPR1.blog_id, 
count(bc.`blog_id`) AS 'comment_count'

FROM ( SELECT bl.blog_id, t.added FROM blog_link bl JOIN type t ON t.id = bl.blog_id

WHERE 
    bl.`section_id` = [$section_id] 
    AND 
    bl.`blog_type` = '[$type]'
GROUP BY 
    bl.`blog_id`
ORDER BY 
    `added` DESC
LIMIT 0,20

) AS EXPR1 JOIN blog_comments bc ON ( bc.item_id = EXPR1.blog_id )

Answer 3

It seems like you just want to get a DISTINCT count, so just add DISTINCT inside the count. Although you will need to add some sort of unique identifier for each comment. Ideally you would have a unique id (ie. auto increment) for each comment, but if you don't you could probably use blog_id+author+timestamp.

SELECT bl.`blog_id`, count(DISTINCT CONCANT(bc.`blog_id`,bc.`author`,bc.`timestamp`) AS 'comment_count',...

That should give you a unique comment count.