SELECT item types not provided by an entity

Question

So I've got some data. There are entities. Entities have an arbitrary number of items. Items can be one of a defined set of types. An entity can have more than one item of a given type. I can get a list of items that an entity has. What I want is to get a list of types that an entity doesn't have an item for.

Here's my schema:

entities
id name
1  Bob
2  Alice

item_types
id      name 
1       red
2       yellow
3       green
4       blue
5       orange

items
entity_id item_type_id name
1         1            apple
1         2            banana
1         3            lime
1         3            tree
2         3            money
2         5            traffic cone

I would like to query Bob's id (1) and get this list:

4   blue
5   orange

And query Alice's id (2) and get:

1   red
2   yellow
4   blue

It's probably starting me in the face. I'm gonna keep working on it but I bet you SO peeps beat me to it. Thank you kindly for your time.

Answer 1

for Bob

SELECT
  t.id, t.name
FROM
  items i
INNER JOIN
  entities e ON e.id = i.entity_id
INNER JOIN
  item_types t ON t.id = i.item_type_id
WHERE
  e.id <> 1

for Alice just swap e.id <> 1 to e.id <> 2

Answer 2

I think this is what you are looking for:

SELECT id, name
FROM item_types
WHERE id NOT IN
(
    SELECT DISTINCT item_type_id
    FROM items
    WHERE entity_id = 1
)

The "entity_id = 1" represents Bob, change it as necessary.

Answer 3

select id, name
from item_types
where id not in
    (select i.item_type_id
    from items i
    inner join entities e
     on e.id = t.entity_id
    where e.Name = 'Bob')

or (sometimes faster, but optimizers are getting better all the time):

select disctinct t.id, t.name
from item_types t
left outer join items i
    on i.item_type_id = t.id
left outer join entities e
    on e.id = i.entity_id
    and e.Name = 'Bob'
where e.id is null

Answer 4

I will rework this to make it better, but here is a working solution

set nocount on
go
drop table #entities
drop table #itemtype
drop table #items
create table #Entities
(
EntityId int,
EntityName  varchar (250)
)
create table #ItemType
(
ItemTypeId int,
ItemTypeName    varchar (250)
)

create table #Items
(
EntityId int,
ItemTypeId int,
ItemName    varchar (250)
)
go
insert into #entities values (1, 'Bob')
insert into #entities values (2, 'Alice')
go
insert into #ItemType values (1, 'red')
insert into #ItemType values (2, 'yellow')
insert into #ItemType values (3, 'green')
insert into #ItemType values (4, 'blue')
insert into #ItemType values (5, 'orange')
go
insert into #Items values (1, 1, 'apple')
insert into #Items values (1, 2, 'banana')
insert into #Items values (1, 3, 'lime')
insert into #Items values (1, 3, 'tree')
insert into #Items values (2, 3, 'money')
insert into #Items values (2, 5, 'traffic cone')
go


;WITH ENTITY AS (
SELECT #Entities.EntityId, EntityName, ItemTypeId, ItemName
FROM #Entities, #Items
WHERE #Entities.EntityId = #Items.EntityId
AND #Entities.EntityName = 'Bob'
) 
SELECT #ItemType.* FROM ENTITY
RIGHT JOIN #ItemType ON ENTITY.ItemTypeId = #ItemType.ItemTypeId
WHERE EntityId is NULL


;WITH ENTITY AS (
SELECT #Entities.EntityId, EntityName, ItemTypeId, ItemName
FROM #Entities, #Items
WHERE #Entities.EntityId = #Items.EntityId
AND #Entities.EntityName = 'Alice'
) 
SELECT #ItemType.* FROM ENTITY
RIGHT JOIN #ItemType ON ENTITY.ItemTypeId = #ItemType.ItemTypeId
WHERE EntityId is NULL