Multiple joins in mysql with 3 tables

i have 3 tables

branch-
  br_id,
  br_name

users 
 user_id
 user_name
 user_type (2 types 'owner' 'admin')
 user_branch (associated branch )
 user_branches (comma seperated branch ids in case of 'admin' type )

item 
  it_id
  it_amount
  it_owner
  it_admin 
  it_activated

Here each item is owned by as user type “owner”

Each item is also associated by a user type “admin”

what I want is list of branches and associated total amount and total count

**Branch**             **total**  **total amount**
some branch Name    5   500
another Branch Name 7   780

How can i do it in single query

i tried this which is showing amount of all branches

SELECT br_name,
count(it_id),
SUM(it_amount),
FROM branch
LEFT JOIN users ON FIND_IN_SET(br_id,user_branches)>0
LEFT JOIN item ON it_admin=ad_id
WHERE it_activated=1
GROUP BY br_name

but I am getting same count and amount in all branches

Answer

I am not sure what you want from the query, so I have taken a guess. My answer query finds the total count and totaled amounts for the items linked to each branch, first by owner and then by admins.

In your query, you have a field “ad_id”:

    LEFT JOIN item ON it_admin=ad_id

I assume that is meant to be “user_id”?

I also notice that, in your query, you have not referenced the owners at all. If you are in fact looking for the results from items linked to branches just by the admins, then you can just remove/ignore the first two subqueries in the following query:

       SELECT   br_name as branchname,
            (SELECT COUNT( DISTINCT it_id) 
                FROM item i1 
                JOIN users u1 ON i1.it_owner = u1.user_id AND i1.it_activated=1
                WHERE u1.user_branch = b.br_id AND u1.user_type = "owner") as ownertotal,
            (SELECT COUNT( DISTINCT it_id) 
                FROM item i2 
                JOIN users u2 ON i2.it_admin = u2.user_id AND i2.it_activated=1
                WHERE FIND_IN_SET(b.br_id, u2.user_branches) != 0 AND u2.user_type = "admin") as admintotal ,
            (SELECT SUM(i3.it_amount) 
                FROM item i3 
                JOIN users u3 ON i3.it_owner = u3.user_id AND i3.it_activated=1
                WHERE u3.user_branch = b.br_id AND u3.user_type = "owner") as owneramount,
            (SELECT SUM(i4.it_amount) 
                FROM item i4 
                JOIN users u4 ON i4.it_admin = u4.user_id AND i4.it_activated=1
                WHERE FIND_IN_SET(b.br_id, u4.user_branches) != 0 AND u4.user_type = "admin") as adminamount
    FROM branch b;