Finding matches where GROUP BY COUNT(*) is 1 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.
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