SELECT from table with count from another

Question

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.

Sample data and desired results would really help. You could also set up a fiddle. — Gordon Linoff
– Gordon Linoff, Commented Aug 3, 2021 at 10:52

Akina · Accepted Answer · 2021-08-03 11:06:38Z

1

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

edited Aug 3, 2021 at 11:06

answered Aug 3, 2021 at 11:00

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

persian-theme · Accepted Answer · 2021-08-03 11:05:01Z

1

you can use following query

select t2.viewId,t2.role as ownRole,t1.usersCount 
from
   (select viewId ,count(*) as usersCount 
   from viewUser
   group by viewId) t1 
join viewUser t2 on t1.viewId = t2.viewId 
where t2.userId = 33

answered Aug 3, 2021 at 11:05

persian-theme

6,6483 gold badges9 silver badges17 bronze badges

Collectives™ on Stack Overflow

SELECT from table with count from another

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related