SELECT from table with count from another

I have 2 mysql tables

View: id, name, description, userId
ViewUser: viewId, userId, role

I need to get all views by user, but also need to get the user role (for each view) and calculate a dynamic number of users used for every view.

So, I need to get all view columns, plus the role of the user which do this request (from ViewUser), plus count of users for each view.

SELECT id, name, description, userId, sum(usersCount), role
FROM (
SELECT v.id, v.name, v.description, vu.userId, count(vu.viewId) as usersCount, vu.role
FROM views v
LEFT JOIN view_users vu on v.id = vu.viewId
group by v.id, vu.userId, vu.role) A
WHERE userId = 33
GROUP BY id, name, description, userId, role;

With this example, I lost the counter for another users, because I use condition userId = 33 at this level. I can do it in only a query, or I just need to use one extra more?

An example:

viewUser:

viewId = 1, userId = 33, role = 'admin'
viewId = 2, userId = 33, role = 'admin'
viewId = 2, userId = 12, role = 'viewer'
viewId = 2, userId = 16, role = 'viewer'
viewId = 3, userId = 33, role = 'viewer'

from this, I need to get 3 lines based on userId = 33 condition:

viewId = 1; ownRole = 'admin'; usersCount = 1
viewId = 2; ownRole = 'admin'; usersCount = 3
viewId = 3; ownRole = 'viewer'; usersCount = 1

edit :

SELECT A.viewId, v.name, v.description, A.usersCount, B.role
FROM
  (SELECT viewId, count(*) as usersCount
  FROM view_users
  WHERE viewId IN (
    SELECT viewId
    FROM view_users
    WHERE userId = 33)
  GROUP BY viewId) A
INNER JOIN (
   SELECT viewId, role
   FROM view_users
   WHERE userId = 33) B ON A.viewId = B.viewId
INNER JOIN views v ON v.id = A.viewId;

This query get the results how I need. But I’m wondering if exists another way, optimized to do that.

Answer

Try to use correlated subquery:

SELECT *,
       ( SELECT COUNT(DISTINCT userId)
         FROM viewUser vu
         WHERE vu.viewId = u.viewId ) usersCount 
FROM viewUser u
WHERE userId = 33

Advertisement

Answer