mysql - 如何用联接(或类似的)替换子查询

Question

嗨，我目前正在参加一些大​​学考试，我有点困于当前的任务

我知道我应该独自解决这个问题，但我陷入困境且绝望

Who lent the same book as Ms. Schmitz did? Print out their last names

但是您不允许使用子查询

<小时/>

这是我的尝试:

SELECT l.Nachname
FROM Ausleihe as a
LEFT JOIN Leser as l on a.LeserOID = l.LeserOID
WHERE ExID IN (
  SELECT ExID
  FROM Ausleihe
         LEFT JOIN Leser L2 on Ausleihe.LeserOID = L2.LeserOID
  WHERE l2.Nachname = 'Schmitz'
);

这很完美，但它带有子查询。

<小时/>

这是数据库结构。

**Buch -> Book**
BuchOID -> BookOID
Titel -> Title
Verfasser -> Author

**Vormerkung -> Reservartion**
LeserOID -> ReaderOID
BuchOID -> BookOID
VormDat -> Reservation date

**Leser -> Reader**
LeserOID -> ReaderOID
Nachname -> Last name
Mail

**Exemplar -> Copy**
BuchOID -> BookOID
ExID -> CopyID
AnschDat -> Date of aqusition

**Ausleihe -> Lending**
BuchOID -> BookOID
ExID -> CopyID
LeserOID -> ReaderOID
LNr -> *PK of this table*
ADat -> Lending date
RDat -> Return date

**Mahnung -> Reminder**
MahnungOID -> ReminderOID
LeserOID - > ReaderOID
BuchOID -> BookOID
ExID -> CopyID
Datum -> date
Betrag -> amount

Answer 1

您可以尝试以下 -

SELECT l.Nachname
FROM Ausleihe as a
LEFT JOIN Leser as l on a.LeserOID = l.LeserOID
JOIN Leser L2 on a.LeserOID = L2.LeserOID
   WHERE l2.Nachname = 'Schmitz'