CODEWITHSUNDEEP
sql
Vinyl Warmth
Vinyl Warmth

Reputation: 2516

How can I write a SQL query to find records related to a record in another table that aren't related to another record

I have a requirement to write a query that finds records related to a record in another table that aren't related to another record.

Below is an example of what I mean. I will happily rewrite this question and title if I can express the question in a better way (advice welcome).

Table company

id  
1
2
3

Table company_partner

id  company_id  company_name
1   1           Nike
2   1           Reebok
3   2           Nike
4   3           Nike

In the above example, I would like all companies partnered with Nike but not if they are also partnered with Reebok. Using the above example that would be companies 2 and 3.

I can write a query that gives me all companies partnered with Nike:

SELECT c.id
FROM company c
INNER JOIN company_partner cp ON c.id = cp.company_id
WHERE 
    cp.company_name = 'Nike'
-- ignore cp.company_name = 'Reebok' ???

I am unclear how I can ignore companies that are also partnered with Reebok?

Upvotes: 0

Views: 49

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

Aggregation provides one straightforward option:

SELECT company_id
FROM company_partner
GROUP BY company_id
HAVING COUNT(CASE WHEN company_name = 'Nike' THEN 1 END) > 0 AND
       COUNT(CASE WHEN company_name = 'Reebok' THEN 1 END) = 0;

Upvotes: 1

Ian Kenney
Ian Kenney

Reputation: 6446

You should be able to use not in - like this:

SELECT c.id
FROM company c
INNER JOIN company_partner cp ON c.id = cp.company_id
WHERE  cp.company_name = 'Nike' 
AND c.id not in (
  select id from company_partner where company_name = 'Reebok'
)

Upvotes: 2

Related Questions