How about a little morning SQL help?

[yllus]

Hey guys - I'm a little stumped with putting together this MySQL query to count the number of comments made to both a user's blog and their photo album. I've got no issues using LEFT OUTER JOINs or GROUP BY clauses, but the two of them together have run me into some trouble.

If I do the below, which is the query put together to the best of my knowledge, I end up with a Cartesian product for an answer. What can I do to fix that?

The query (MySQL):

SELECT au.username,
au.name_first,
au.name_last,
au.account_id,
au.city,
COUNT(mec.media_file_comment_id) AS num_comments_m,
COUNT(bec.blog_entry_comment_id) AS num_comments_b
FROM account_user au
LEFT OUTER JOIN media m, media_folder mf, media_file mfile, media_file_comment mec
ON (au.account_id = m.account_id AND m.media_id = mf.media_id AND mf.media_folder_id = mfile.media_folder_id AND mfile.media_file_id = mec.media_file_id)
LEFT OUTER JOIN blog b, blog_entry be, blog_entry_comment bec
ON (au.account_id = b.account_id AND b.blog_id = be.blog_id AND be.blog_entry_id = bec.blog_entry_id AND m.account_id = b.account_id)
GROUP BY au.account_id
LIMIT 10

The schema (MySQL):

CREATE TABLE account_user (
user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
account_id INTEGER);

CREATE TABLE blog (
blog_id INTEGER NOT NULL AUTO_INCREMENT,
account_id INTEGER);

CREATE TABLE blog_entry (
blog_entry_id INTEGER NOT NULL AUTO_INCREMENT,
blog_id INTEGER);

CREATE TABLE blog_entry_comment (
blog_entry_comment_id INTEGER AUTO_INCREMENT NOT NULL,
blog_entry_id INTEGER NOT NULL);

CREATE TABLE media (
media_id INTEGER NOT NULL AUTO_INCREMENT,
account_id INTEGER);

CREATE TABLE media_folder (
media_folder_id INTEGER NOT NULL AUTO_INCREMENT,
media_id INTEGER NOT NULL);

CREATE TABLE media_file (
media_file_id INTEGER NOT NULL AUTO_INCREMENT,
media_folder_id INTEGER NOT NULL);

CREATE TABLE media_file_comment (
media_file_comment_id INTEGER NOT NULL AUTO_INCREMENT,
media_file_id INTEGER NOT NULL);

 

[MrChad]

Why is this in Off Topic?

Although I'm not too familiar with MySQL's idiosyncracies, don't you need to include au.username, au.name_first, au.name_last and au.city in your GROUP BY clause?

 

[MrChad]

Try this:

SELECT
au.username,
au.name_first,
au.name_last,
au.account_id,
au.city,
COUNT(mec.media_file_comment_id) AS num_comments_m,
COUNT(bec.blog_entry_comment_id) AS num_comments_b
FROM
account_user au
LEFT OUTER JOIN (SELECT m.account_id, mec.media_file_comment_id FROM media m, media_folder mf, media_file mfile, media_file_comment mec WHERE m.media_id = mf.media_id AND mf.media_folder_id = mfile.media_folder_id AND mfile.media_file_id = mec.media_file_id) media ON au.account_id = media.account_id
LEFT OUTER JOIN (SELECT b.account_id, bec.blog_entry_comment_id FROM blog b, blog_entry be, blog_entry_comment bec WHERE b.blog_id = be.blog_id AND be.blog_entry_id = bec.blog_entry_id AND m.account_id = b.account_id) blogs ON au.account_id = blogs.account_id
GROUP BY au.username, au.name_first, au.name_last, au.account_id, au.city
LIMIT 10

 

[yllus]

Programming is pretty slow, figured I'd shoot this off and have it solved by someone better at SQL than me in a hurry. In any case, OT could use something challenging. Think of it as a really nerdy brainteaser.

No need to do a GROUP BY on more than one key in this case, I think. (I tested what you mentioned for the hell of it and it made no difference, same Cartesian product result.)

Edit: Trying your query idea now...

 

[Nitemare]

How about a little morning wrong forum Nik?


Sorry, couldn't resist

 

[MrChad]

Did it work?

 

[yllus]

I don't think that MySQL likes the stucture you set up your query in, but the idea of using subqueries is a solid one - lemme rework what you got and I'll post back when I (eventually) solve this. Thanks.

 

[mitmot]

where's Nik?

 

[bmacd]

Originally posted by: mitmot
where's Nik?

hopefully at the bottom of the lake wearing cement shoes.

-=bmacd=-

 

[Dessert Tears]

Originally posted by: yllus
If I do the below, which is the query put together to the best of my knowledge, I end up with a Cartesian product for an answer. What can I do to fix that?

Do you mean that if B = # of blog comments and P = # of photo album comments, you're getting B*P returned for both COUNTs? I think this is happening because you joined media and blog together. I would take off the COUNTs and the GROUP BY for debugging purposes.

My guess is that if you have m1, m2, and b1 (media file comment ids, blog comment ids), the following happens:
2 rows: m1, b1; m2, b1
instead of
3 rows: m1; m2; b1

I would fix it by making 2 subqueries, 1 against media and 1 against blog and combining them with a UNION or by adding their results.