CODEWITHSUNDEEP
mysqlsqldatabase
Teilmann
Teilmann

Reputation: 2168

Mysql join table and count rows

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:

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

comments table

Upvotes: 0

Views: 135

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 1

DarkSide
DarkSide

Reputation: 3709

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

Upvotes: 3

Related Questions