How can I write a SQL script that shows if a record is related to other specific records?-CodePudding

I have 2 tables:

-- company
id  
1
2
3

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

I need a SQL script that shows if a company has a company_partner record. The output should be

company has_nike_partner  has_rebook_partner
1       true              true
2       true              false
3       true              false

I think I can use aggregation to see if there is a related partner but not sure how to access it in the SELECT statement

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

CodePudding user response：

You are on the right track, but your aggregated CASE expressions should be in the select clause:

SELECT
    c.id,
    CASE WHEN COUNT(CASE WHEN cp.company_name = 'Nike' THEN 1 END) > 0
         THEN 1 ELSE 0 END AS has_nike_partner,
    CASE WHEN COUNT(CASE WHEN cp.company_name = 'Reebok' THEN 1 END) > 0
         THEN 1 ELSE 0 END AS has_reebok_partner
FROM company c
LEFT JOIN company_partner cp
    ON cp.company_id = c.id
GROUP BY c.id;