How to make 2 unique LEFT JOINs on the same table cell?

advertisements

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


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