How to join 1 table twice in the same query and keep results separate

Question

Hi, we're building a scheduler system and have a couple of situations where we're trying to get some notes from a table to display. We've looked at other answers and none seem to quite match this problem.

The bookings table holds a numeric reference to both a client note (if present) and a stylist note (if present).

The notes table holds both client and stylist notes, each indexed by a unique numeric index

We've got our query working when we just want to read in the client notes:

SELECT bookings.bookingID, UNIX_TIMESTAMP(bookings.startDate) AS start_date, UNIX_TIMESTAMP(bookings.endDate) as end_date, clientDetails.firstName, clientDetails.lastName, clientDetails.phone1, clientDetails.phone2, clientDetails.email, services.name, services.serviceID, cNotes.note as client_notes, sNotes.note as stylist_note
FROM bookings 
LEFT JOIN clientDetails ON bookings.clientID = clientDetails.clientID
LEFT JOIN services ON bookings.serviceID = services.serviceID
LEFT JOIN notes ON bookings.clientNotes = notes.notesID
WHERE bookings.startDate >= '" . $start_date . "' AND  bookings.endDate <= '" . $end_date . "' AND bookings.stylistID = '" . $stylist_id . "'
ORDER BY bookings.startDate ASC;

Using this logic we can access the cient notes from the results array in php simply as: $results_array['note']

What we also want to be able to do is to get the stylist note for that booking as well, something like $results_array['stylist_note'].

How can we join the notes table again this time using:

LEFT JOIN notes ON bookings.stylistNotes = notes.notesID

BUT be able to reference these stylist notes separately from the client notes.

Thanks so much for any assistance.

Answer 1

You should be able to alias the table and columns:

SELECT ..., stylistnotes.note AS stylist_note
FROM ...
LEFT JOIN notes stylistnotes ON bookings.stylistNotes = stylistnotes.notesID