Mysql join table and count rows

Question

I have two tables: comments & photos

Every comments is "connected" to a certain photo by the photo id. I want to get all photos, from a certain author(author_id), and a count of all the comments on the specific photo. I'm sorry for my english, but i think that's the best way i can describe it.

I want a table with:

id (photo table)
rating (photo table)
created_at (photo table)
number_of_comments (comments table)

What i've tried so far (with syntax error):

"SELECT p.id, p.rating, p.created_at, x.*
FROM photos p 
LEFT JOIN 
(
    SELECT photo_id, COUNT(*) as cc
    FROM comments
    GROUP BY photo_id
) x 
ON x.photo_id= p.id"

error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT p.id, p.rating, p.created_at, x.* FROM photos p LEFT JOIN ( SELECT' at line 1"

photos table

id
author_id
filename
caption
rating
flags
is_active

comments table

id
comment
author_id
photo_id
created_at
flags
is_active

"What i've tried so far (with syntax error)" - can you share the error please? — Mureinik
– Mureinik, Commented May 9, 2014 at 20:42
Still there is no syntax error in your query. Try copying it directly from your source code here. — DarkSide
– DarkSide, Commented May 9, 2014 at 20:47

DarkSide · Accepted Answer · 2014-05-09 20:46:05Z

3

This way it'll work much faster with less resources need and will be simpler to read :)

SELECT p.id, p.rating, p.created_at, count(c.id) AS cnt
FROM photos p 
    LEFT JOIN comments c ON c.photo_id=p.id
GROUP BY p.id

answered May 9, 2014 at 20:46

DarkSide

3,7191 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

DarkSide Over a year ago

you can add them in group by, but I believe they are not needed, because p.id is primary key which is unique for each photo.

Teilmann Over a year ago

Even though my query actually seems to work, apparently, this way is much smarter than my solution! Upvoted and will be marking this as the answer in 2 min, when i am able to. Thanks buddy

VMai Over a year ago

rating and created_at should be the same for every id, so MySQL wouldn't produce wrong results - and MySQL would execute the query without error.

DarkSide Over a year ago

... and much faster, because no cursor is needed

Teilmann Over a year ago

I will also be using this solution, not mine :)

|

Teilmann · Accepted Answer · 2014-05-09 22:54:08Z

1

As a note, the fastest way to write this query is likely to be:

SELECT p.*,
       (SELECT count(*) FROM comments c WHERE c.photo_id = p.id) AS cnt
FROM photos p
WHERE p.author_id = 1;

For best performance, create an index on comments(photo_id).

I offer this because performance is mentioned as a consideration. The join and group by method is also a good way to write the query.

edited May 9, 2014 at 22:54

Teilmann

2,1787 gold badges29 silver badges57 bronze badges

answered May 9, 2014 at 22:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

Teilmann Over a year ago

This does not return what the above solution does. I want the query to return the photo info, of a certain author(author_id) AND the number of commments on the photo. Your's returns all photos. I have suggested an edit :) ty

Gordon Linoff Over a year ago

@ThomasTeilmann . . . You would add a where condition if you want to get something about a particular author. It looks like your original syntax error is due to the double quote at the beginning of the SQL statement.

Teilmann Over a year ago

Yes i figured that out :) Copy paste error it would seem. ty :)

Teilmann Over a year ago

Your query suits my problem perfectly, and since i found out the previous accepted answer actually didnt do the trick, i accepted yours as the answer.

DarkSide Over a year ago

@ThomasTeilmann in your question above you didn't ask for author_id thing, so ... :) Also this solution works only for 1 particular author. You better do

SELECT p.author_id, p.id, (SELECT count(*) ...) cnd FROM photos p WHERE p.author_id=1 (optional where clause) GROUP BY p.author_id, p.id

. That'll return each author + each photo + count of comments for each photo.

Collectives™ on Stack Overflow

Mysql join table and count rows

2 Answers 2

8 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related