CODEWITHSUNDEEP
mysqlsqljoin
AlleXyS
AlleXyS

Reputation: 2598

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.

Upvotes: 0

Views: 80

Answers (2)

Akina
Akina

Reputation: 42622

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

Upvotes: 1

persian-theme
persian-theme

Reputation: 6638

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

Upvotes: 1

Related Questions