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.