Finding matches where GROUP BY COUNT(*) is 1

Question

Say I have tables

organisation:

id | name
---------
1    ACME
3    MGM

org_type:

type_id | org_id
----------------
1         1
2         1
2         3

How do I query a list of org names where there type is 2, and no other type is linked with that organisation? In other words, a query that returns only organisation with org_id of 3 from above data.

I'm trying queries like

SELECT o.name
FROM organisation o
JOIN org_type ot ON o.id=ot.org_id
WHERE ot.type_id = 2
GROUP BY ot.org_id
HAVING COUNT(*) = 1

But obviously that just returns the list of ALL orgs where a link to type_id 2 exists. I only want those where no other link exists.

Answer 1

This query should help you out.

It works with an delivered table named org_type what filters out double org_id.

That delivered table will be matched with where filter org_type.type_id = 2 to make sure that type_id = 2

SELECT 
   org_type.type_id
 , org_type.org_id
 , organisation.name
FROM (
 SELECT 
     type_id
   , org_id
 FROM 
   org_type
 GROUP BY
   org_id
 HAVING 
   COUNT(*) = 1
) AS 
  org_type
INNER JOIN 
  organisation
ON
  org_type.org_id = organisation.id
WHERE 
  org_type.type_id = 2 

Answer 2

This is kludgy:

SELECT o.name
    FROM organisation o
    JOIN org_type ot ON o.id=ot.org_id
    GROUP BY ot.org_id
    HAVING  GROUP_CONCAT(DISTINCT ot.type_id) = '2';

Answer 3

Please check whether it meets your requirement:

select * from org
where not exists ( select 1 from org_type
where org_type.type_id <> 2
and   org_type.org_id = org.id
)
and exists ( select 1 from org_type
where org_type.type_id = 2
and   org_type.org_id = org.id
)