CODEWITHSUNDEEP
sqljoin
u2622
u2622

Reputation: 3011

SQL Join tables - detecting presence of some tuples but not others

I've got two primary tables: codes and categories. I've also got a join table code_mappings which associates codes with categories.

I need to be able to determine which codes are mapped to one group of categories, but not mapped to another. Been banging my head against this for a while, but am completely stuck.

Here's the schema:

create table codes(
  id int,
  name varchar(256));

create table code_mappings(
  id int,
  code_id int,
  category_id int);

create table categories(
  id int,
  name varchar(256));

And some seed data:

INSERT INTO categories VALUES(1, 'Dental');
INSERT INTO categories VALUES(2, 'Weight');
INSERT INTO categories VALUES(3, 'Other');
INSERT INTO categories VALUES(4, 'Acme Co');
INSERT INTO categories VALUES(5, 'No Name');

INSERT INTO codes VALUES(100, "big bag of cat food");
INSERT INTO codes VALUES(200, "healthy doggie treatz");

INSERT INTO code_mappings VALUES(50, 200, 1);
INSERT INTO code_mappings VALUES(51, 100, 4);
INSERT INTO code_mappings VALUES(52, 100, 3);

How would I write a query that will give me the codes that are mapped to one of categories (1,2,3) but not to one of categories (4,5)?

Upvotes: 0

Views: 33

Answers (2)

wildplasser
wildplasser

Reputation: 44250

SELECT *
FROM codes co
WHERE EXISTS (
    SELECT *
    FROM code_mappings ex
    WHERE ex.code_id = co.id
    AND ex.category_id IN (1,2,3)
    )
AND NOT EXISTS (
    SELECT *
    FROM code_mappings nx
    WHERE nx.code_id = co.id
    AND nx.category_id IN (4,5)
    )
    ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270693

This is an example of a set-within-sets query. I like to approach these using group by and having, because I find that the most flexible approach:

select cm.code_id
from code_mappings cm
group by cm.code_id
having sum(case when cm.category_id in (1, 2, 3) then 1 else 0 end) = 1 and
       sum(case when cm.category_id in (4, 5) then 1 else 0 end) = 0;

Each condition in the having clause implements exactly one of the conditions. You said one code of 1, 2, or 3, hence the = 1 (if you wanted at least one of these three, it would be > 0). You said no 4 or 5, hence = 0.

Upvotes: 1

Related Questions