CODEWITHSUNDEEP
mysqlsql
DevMan
DevMan

Reputation: 556

Finding many matches to one row in the same table and getting results from a second table based on the results

I know that the question title may not be quit clear to understand but I try to explain:

users_table:

id | name | admin   | property_id
-----------------------------------
 1  | x    | 1       | 0
 2  | y    | 1       | 0
 3  | z    | 0       | 1
 5  | t    | 0       | 2
 6  | u    | 0       | 2
 7  | o    | 0       | 2

users_table has two or more records which are admin and some other records which belong to one of these admin records by matching the property_id with the id. In the end what I want is the admin row data and the count of its properties. This is what should be the output from the first part of the query:

  id | name | admin   | property_count
-----------------------------------
  1  | x    | 1       | 1
  2  | y    | 1       | 3

Until now I know how to get the desired results but here begins the problem.I have another table 

sells_table:

id | seller_id | sell_amount
----------------------------
 1 | 3         | 250
 2 | 5         | 120
 3 | 7         | 100
 4 | 5         | 200

So this is the logic: every admin has many properties and each property has many sells. I want all records for each admin from the users_table plus the count of its property_id. And then query the sells_table in a way where for each property of each admin the number of sells and the sum of the total sells gets calculated. for example this should be the result for the admin with the id 2 and the name y:

  name | properties | property_sells | property_amount
  --------------------------------------------------------
   y   |    3       |     3          |   420

y has 3 properties. Property with id 5 which belongs to y(admin) has two sells and id 7 which also belongs to y(admin) has one sell and the sum of these 3 sells is 420.

Upvotes: 0

Views: 46

Answers (2)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/36834d/2

SELECT u.id, U.name, u.admin, COUNT(DISTINCT ut.id) property_count, SUM(st.sell_amount)
FROM users_table u
LEFT JOIN users_table ut
ON u.id = ut.property_id
LEFT JOIN sells_table st
ON ut.id  = st.seller_id
WHERE u.admin = 1
GROUP BY u.id, u.admin, u.name

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think this is what you want:

select ua.id, ua.name, ua.admin, count(distinct u.id) as property_count,
       sum(s.sell_amount) as amount
from users_table ua left join
     users_table u
     on ua.id = u.property_id left join
     sales s
     on s.seller_id = u.id
where ua.admin = 1
group by ua.id, ua.name, ua.admin;

Upvotes: 1

Related Questions