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.

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 
1

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';
  • Or without the DISTINCT if type_id is unique per org_id. – Andriy M Dec 15 '16 at 8:13
0

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
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.