StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Use: </p> <pre><code>SELECT c.title, c.initial, c.surname, c.labelno, c.email, br.booking date FROM CLIENT c JOIN BOOKING_RECORD br ON br.labelno = c.labelno AND br.bookingdate BETWEEN STR_TO_DATE(?Param1, '%Y-%m-%d') AND STR_TO_DATE(?Param2, '%Y-%m-%d') WHERE c.label_no NOT IN (SELECT t.label_no FROM BOOKING_RECORD t WHERE br.bookingdate BETWEEN STR_TO_DATE(?Param3, '%Y-%m-%d') AND STR_TO_DATE(?Param4, '%Y-%m-%d') </code></pre> <ul> <li>Param1 & 2 represent the dates in the past you want to check for the existence of IE Jan of last year. </li> <li>Param3 & 4 represent the dates you want to check for that the client does not have.</li> <li>Using functions on columns for evaluation means that, assuming an index exists for the column, the index can not be used. </li> </ul> <h2>Caveat:</h2> <hr> <p>In MySQL, <code>NOT IN</code> and <code>LEFT JOIN/IS NULL</code> are faster than <code>NOT EXISTS</code>. For <a href="http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/" rel="nofollow noreferrer">more information, see this link for details</a>.</p> <h2>Reference:</h2> <hr> <ul> <li><a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date" rel="nofollow noreferrer">STR_TO_DATE</a></li> <li><a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format" rel="nofollow noreferrer">DATE_FORMAT</a>: For the list of supported date masks to use in STR_TO_DATE</li> </ul>

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