Re: Records from one table not in another

[Peter Brawley]

I won't do your homework for you, but the following should nudge you further along. Taking the first part of your example ...

drop table if exists item, buys;
create table item(itemid smallint);
insert into item values(1),(2),(3),(4);
create table buys(itemid smallint, userid smallint);
insert into buys values(1,1),(2,1),(3,1);

... to find the items user 1 has not bought, do an exclusion join from items to user 1's buys:

select a.itemid 
from item a
left join (
  select itemid 
  from buys 
  where userid=1
) b using(itemid)
where b.itemid is null;

You just need to generalise that for all users. You'll find three different ways to do that under
"All X for which all Y are Z" and "All X for which all Y are Z (relational division)" at www.artfulsoftware.com/queries.php.