1

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.

0

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.

  • Thanks, it works. Regarding EXPLAIN, here there's the row of subscriptions table (table,type,possible_keys,key,key_len,ref,rows,Extra): subscriptions|index|NULL|id_item|6|NULL|5|Using index. – argon argon Dec 31 '13 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.