Mysql – Count records in a related table

countMySQL

I have three tables: items, members, subscriptions.
One item can be get by 1 or more members (one member can get more items), so subscriptions represents the many-to-many relationship between items and members.
So we have:

  • items(id, name)
  • members(id, name, email)
  • subscriptions(*id_item*, *id_member*)

And now my problem: I have a quite comlicated query which manage items and outputs items data, I want to add to this output a column that contains the number of members who subscribed that item.
I wrote this query but I don't want to use sub select statement

SELECT
(SELECT count(*) AS current_sub from subscriptions AS s where s.id_item = i.id) AS current_members,
  -- ..... other code....
FROM items AS i
WHERE
// ....other code...

I use MySQL 5.5. Thanks in advance.

Best Answer

This one might work better:

SELECT
*
FROM
items AS i
INNER JOIN (SELECT id_item, COUNT(*) AS count_subs FROM subscriptions GROUP BY id_item) counts
      ON i.id = counts.id_item
WHERE
....

Post the result of the query with EXPLAIN keyword in front of SELECT. Then we can see, if and how indexes are used.

Related Question