How can I get all rows in a Sqlite table that don't have a relationship defined by a mapping table to rows in a 3rd table?

Question

This seems to be the hardest thing to search for - I can find a lot of "how do I find empties in two tables", or how to do so for non-sqlite, but...

Three tables, item (id, name), user (id, name) and item_user (item_id, user_id) - the last table connects the first two
Three users, Bob, Jane, Danny
Two items, hammer, nail

How do I find the users who haven't made an order for an item?

So, if...

bob has ordered a hammer and a nail
so has Jane
Danny has only ordered a hammer

...then I want to return one row:

user.name   item.name
---------   ---------
Danny       nail

Can I do a search to show this? In sqlite?

@anonyXmous sure, uh, let's say an id, and name column in both the item & user tables. and the item_user table has a item_id and a user_id column — bharal, Mar 16 '18 at 04:59

Uyghur Lives Matter · Answer 1 · 2018-03-17T00:45:28.770

If I'm understanding your question correctly, you want to find every item that each user has not ordered? That can be accomplished with the following query:

SELECT
    user.name AS user_name,
    item.name AS item_name

-- Get all users and items.
FROM user
CROSS JOIN item

-- Exclude items that users have ordered.
WHERE NOT EXISTS (
      SELECT *
      FROM item_user
      WHERE item_user.item_id = item.id
          AND item_user.user_id = user.id
  );

This yields:

user_name | item_name
----------+----------
'Danny'   | 'nail'

Query used to construct tables:

CREATE TABLE item (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

INSERT INTO item (name)
VALUES ('hammer'), ('nail');

CREATE TABLE user (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

INSERT INTO user (name)
VALUES ('Bob'), ('Jane'), ('Danny');

CREATE TABLE item_user (
  item_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (item_id) REFERENCES item (id),
  FOREIGN KEY (user_id) REFERENCES user (id)
);

INSERT INTO item_user (user_id, item_id)
VALUES
  (1, 1), -- Bob, hammer
  (1, 2), -- Bob, nail
  (2, 1), -- Jane, hammer
  (2, 2), -- Jane, nail
  (3, 1); -- Danny, hammer

How can I get all rows in a Sqlite table that don't have a relationship defined by a mapping table to rows in a 3rd table?

1 Answers1