Get all entries from one table together with the count of these entries in another table.

Question

I've got 3 tables:

• users (id, name, ...)
• items (id, name, ...)
• downloads (user_id, item_id, ...)

How do I get all users together with the number of downloads they have?

Answer 1

This should do it:

SELECT u.name as name, count(*) as downloads
FROM users u, items i, downloads d
WHERE u.id = i.user_id AND i.user_id = d.user_id;

I had to invent items.user_id, but it should be available.

Answer 2

How do I get all users together with the number of downloads they have?

Use:

SELECT u.name,
       COUNT(d.user_id) 'num_downloaded'
FROM USERS u
OIN DOWNLOADS d ON d.user_id = u.user_id
GROUP BY u.name

If you want to see how many times a user has downloaded a specific item:

SELECT u.name 'user_name',
       i.name 'item_name',
       COUNT(d.user_id) 'num_downloaded'
FROM USERS u
JOIN DOWNLOADS d ON d.user_id = u.user_id
JOIN ITEMS i ON i.item_id = d.item_id
GROUP BY u.name, i.name