Stuck with a complex MySQL query

debugcn Published at Dev

mbk

Happy New Year 2014 to all StackOverFlow contributors and users.

I have searched and gone through the available topics similar to mine. But, failed to find that satisfies my requirements. Hence, posting it here.

I have four tables: "Organization", "Members", "Resource" and "member-resource"

The schema can be found here at SQLFiddle

I want to find out the number of distinct res_name from Resource table that have more than one res_prop for each organizations. Expected output would be as follows:

| org_id       | res_count      |
| 2            | 4              |
| 3            | 1              |

Any help in this regard would be appreciated.

Thanks and regards,

Mihai

SELECT org_id,count(DISTINCT res_name) as res_count
FROM
(SELECT o.org_id,r.res_name,COUNT(DISTINCT res_prop)as distinct_res_prop
FROM organization o
INNER JOIN members m USING (org_id)
INNER JOIN `member-resource` mr USING (mem_id)
INNER JOIN resource r USING (res_id)
GROUP BY o.org_id,r.res_name
HAVING COUNT(distinct_res_prop) > 1
 )T1
GROUP BY org_id

In HAVING clause add COUNT

Fiddle

Collected from the Internet

Please contact [email protected] to delete if infringement.