Databases
StackOverflow2013
Postsdbo
POHow to join 1 table twice in the same query and keep results separate
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to join 1 table twice in the same query and keep results separate
    text
    copied!<p>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.</p> <p>The bookings table holds a numeric reference to both a client note (if present) and a stylist note (if present).</p> <p>The notes table holds both client and stylist notes, each indexed by a unique numeric index</p> <p>We've got our query working when we just want to read in the client notes:</p> <pre><code>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 &gt;= '" . $start_date . "' AND bookings.endDate &lt;= '" . $end_date . "' AND bookings.stylistID = '" . $stylist_id . "' ORDER BY bookings.startDate ASC; </code></pre> <p>Using this logic we can access the cient notes from the results array in php simply as: $results_array['note']</p> <p>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']. </p> <p>How can we join the notes table again this time using:</p> <pre><code>LEFT JOIN notes ON bookings.stylistNotes = notes.notesID </code></pre> <p>BUT be able to reference these stylist notes separately from the client notes.</p> <p>Thanks so much for any assistance.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload