MySQL Count Items In Category

Question

I don't understand MySQL very well, here are the table structures I am using.

users

id | first_name | last_name | username | password

categories

id | user_id | name | description

links

id | user_id | category_id | name | url | description | date_added | hit_counter

I am trying to return a result set like this, to give information about the category for a user that includes how many links are in it.

id | user_id | name | description | link_count

At the moment I have this query, but it only returns rows for categories that have links. It should return rows for categories that do not have any links (empty categories).

SELECT categories.*, COUNT(links.id) FROM categories LEFT JOIN links ON categories.id=links.category_id;

How to do this query? Thanks.

Answer 1

we can't do select table dot "star" with an aggregate.

what you wanna do is something like (pseudocode):

select
    categories.field1,
    categories.field2,
    {etc.}
    count(links.id)
from categories
left join links
    on categories.id = links.category_id
group by
    categories.field1,
    categories.field2,
    {etc.}

iow: you're missing the group by code-block to get the right aggregate in your query result set.

Answer 2

To mold alien052002's answer to fit your specific question, the following (untested) should work:

select c.id,
    c.user_id,
    c.name,
    c.description,
    count(l.link_count)
from categories c
left join links l on l.category_id = c.id
group by c.id, c.user_id, c.name, c.description

Answer 3

try this

SELECT categories.*, COUNT(links.id) FROM categories LEFT JOIN links ON categories.id=links.category_id group by categories.id;