如何在同一个表格单元格上执行 2 个唯一的 LEFT JOIN？

发布于 2024-09-16 18:34:56 字数 874 浏览 9 评论 0原文

在 mysql 中，我想在同一个表单元格上执行 2 个唯一的 LEFT JOIN。

我有两张桌子。

一张表列出了各个客户，并为每个客户提供了 clientNoteID 和 StaffNoteID 条目。 clientNoteID和staffNoteID都是notesTable中笔记存储的唯一noteID的整数引用。

clientsTable:

clientID | clientName | clientNoteID | staffNoteID

notesTable:

noteID | note

我希望能够从notesTable 中选择clientNoteID 引用的注释和staffNoteID 引用的注释。

我没有看到任何方法可以为左连接添加别名，例如：（

SELECT FROM clientsTable clientsTable.clientID, clientsTable.clientName, clientsTable.clientNoteID, clientsTable.stylistNoteID
LEFT JOIN notes on clientTable.clientNotesID = notes.noteID
LEFT JOIN notes on clientTable.staffNoteID = notes.noteID as staffNote

并不是说我认为这真的很有意义）

那么，我如何查询以便可以在最后打印出来：

clientName | clientNote | staffNote

原文

In mysql I'd like to do 2 unique LEFT JOINs on the same table cell.

I have two tables.

One table lists individual clients and has a clientNoteID and staffNoteID entry for each client. clientNoteID and staffNoteID are both integer references of a unique noteID for the note store in the notesTable.

clientsTable:

clientID | clientName | clientNoteID | staffNoteID

notesTable:

noteID | note

I'd like to be able to select out of the notesTable both the note referenced by the clientNoteID and the note referenced by the staffNoteID.

I don't see any way to alias a left join like:

SELECT FROM clientsTable clientsTable.clientID, clientsTable.clientName, clientsTable.clientNoteID, clientsTable.stylistNoteID
LEFT JOIN notes on clientTable.clientNotesID = notes.noteID
LEFT JOIN notes on clientTable.staffNoteID = notes.noteID as staffNote

(not that i think that really makes too much sense)

So, how could I query so that I can print out at the end:

clientName | clientNote | staffNote

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暮年慕年 2024-09-23 18:34:56

当您连接表时，alas 必须紧接在表名称之后，而不是连接条件之后。试试这个：

SELECT clientsTable.clientName, n1.note AS clientNote, n2.note AS staffNote
FROM clientsTable 
LEFT JOIN notes AS n1 ON clientTable.clientNotesID = n1.noteID
LEFT JOIN notes AS n2 ON clientTable.staffNoteID = n2.noteID

When you join a table the alas must be immediately after the table name, not after the join condition. Try this instead:

SELECT clientsTable.clientName, n1.note AS clientNote, n2.note AS staffNote
FROM clientsTable 
LEFT JOIN notes AS n1 ON clientTable.clientNotesID = n1.noteID
LEFT JOIN notes AS n2 ON clientTable.staffNoteID = n2.noteID

回复收藏 0 原文

娇妻 2024-09-23 18:34:56

你需要给表本身起别名

SELECT FROM clientsTable clientsTable.clientID, clientsTable.clientName, clientsTable.clientNoteID, clientsTable.stylistNoteID
LEFT JOIN notes a on clientTable.clientNotesID = a.noteID
LEFT JOIN notes b on clientTable.staffNoteID = b.noteID

you need to alias the tables themselves

SELECT FROM clientsTable clientsTable.clientID, clientsTable.clientName, clientsTable.clientNoteID, clientsTable.stylistNoteID
LEFT JOIN notes a on clientTable.clientNotesID = a.noteID
LEFT JOIN notes b on clientTable.staffNoteID = b.noteID

回复收藏 0 原文

小伙你站住 2024-09-23 18:34:56

SELECT CT.clientName, N1.note AS clientNote, N2.note AS staffNote
FROM clientsTable CT
LEFT JOIN notes N1 on CT.clientNotesID = N1.noteID
LEFT JOIN notes N2 on CT.staffNoteID = N2.noteID

SELECT CT.clientName, N1.note AS clientNote, N2.note AS staffNote
FROM clientsTable CT
LEFT JOIN notes N1 on CT.clientNotesID = N1.noteID
LEFT JOIN notes N2 on CT.staffNoteID = N2.noteID

回复收藏 0 原文

~没有更多了~