Rufen Sie Datensätze ab, die in der verbundenen Tabelle nicht vorhanden sind

Question

Ich habe drei Tabellen in einer MySQL-Datenbank. Die erste Tabelle enthält Benutzer und die zweite Tabelle enthält Projekte. Nachfolgend finden Sie die Struktur dieser beiden.

users
------
userid (int)
username (varchar)

items
------
itemid (int)
name (varchar)

Die dritte Tabelle ist die Join-Tabelle.

user_items
----------
userid (int)
itemid (int)

Ich möchte eine Abfrage, die eine Liste der Benutzer und der Projekte zurückgibt, die ihnen nicht zugewiesen sind.

Im Beispiel Ich habe folgende Benutzer

userid      username
1           john
2           tim
3           mark

Ich habe auch die folgenden Artikel

itemid      name
1           book
2           pen
3           backpack

In meiner Join-Tabelle

userid    itemid
1         1
1         3
2         1
2         2
2         3
3         2

Ich möchte also eine Liste der Elemente erhalten, die nicht dem Benutzer gehören, zum Beispiel:

userid    itemid
1         2
3         1
3         3

Was ist die beste Abfrage, um solche Ergebnisse zu erhalten? Ich versuche es mit einigen Left-Joins, Left-Outer-Joins, Left-Joins usw. ohne Erfolg.

Edit 1: Bisher habe ich folgende Abfragen ausprobiert:

SELECT con.userid, i.itemid FROM items i
LEFT JOIN (
SELECT u.id as userid, ui.itemid
FROM users u
INNER JOIN user_items ui ON u.userid = ui.itemid
) con ON i.itemid = con.itemid
WHERE con.itemid IS NULL

Answer 1

您通常会交叉联接用户和产品以生成所有可能的组合，然后过滤掉桥接表中已存在的关联：

select u.userid, p.itemid
from users u
cross join items i
where not exists (
    select 1 from user_items ui where ui.userid = u.userid and ui.itemid = i.itemid
)

为了提高性能，您需要 user_items(userid, itemid) 上的索引（如果您对这些列有唯一 约束，它应该已经存在）。

我们还可以用反左连接来表达逻辑：

select u.userid, p.itemid
from users u
cross join items i
left join user_items ui on ui.userid = u.userid and ui.itemid = i.itemid
where ui.userid is null