How do I do 2 unique LEFT JOINs on the same table cell?

Question

Hi,

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

Answer 1

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 

Answer 2

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

Answer 3

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