Retrieving like counts on entries from SQL

Question

I've been adding a like feature to an entries database... here's the structure of the DBs:

**Users**
user_id
user_name
etc.

**Entries**
entry_id
entry_content
etc.

**Likes**
user_id
entry_id

(It's a little more complicated than that, there are groups/categories, but that should explain it fine...) Here's the SQL query I'm working with at the moment:

SELECT 
entries.*,
DATE_FORMAT(entry_date, "%M %D, %Y") as entry_date,
groups.group_short_name,
users.user_name, users.user_id,
FROM entries 
INNER JOIN groups ON groups.group_id = entries.group_id 
INNER JOIN users ON users.user_id = entries.user_id 
ORDER BY entry_date DESC

I'm trying to also retrieve likes per entry with this query and wondering if it is possible. I've been trying:

COUNT(DISTINCT likes.like_id) as likes

with

LEFT JOIN likes ON likes.entry_id = entries.entry_id

But I don't think that is anywhere near right. Am I way off? Is this possible? Hope it all made sense.

Thanks for the help in advance.

score 1 · Accepted Answer · answered Oct 25 '10 at 16:58

1

Give your tables aliases, for one..

FROM entries e

Then add a column query:

select e.*, (select count(*) from Likes where entry_id = e.entry_id) as entry_likes

answered Oct 25 '10 at 16:58

Fosco

38,138
7
87
101

Oops hit enter too fast... just wondering, why should I need to give my tables aliases? Is it so bad to write 'entries' instead of 'e' each time? I'm only doing it a couple times... – Ian Storm Taylor Oct 25 '10 at 20:34
@Ian I always do it for a number of reasons.. It makes writing queries easier, you're more informed and precise when creating self joins and sub-queries, and I haven't found a good reason not to. :) – Fosco Oct 25 '10 at 22:33

score 0 · Answer 2 · answered Oct 25 '10 at 16:57

0

Add:

GROUP BY entries.entry_id

See if that works.

answered Oct 25 '10 at 16:57

robbrit

17,560
4
48
68

Retrieving like counts on entries from SQL

2 Answers2