StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There's a lot going on there...</p> <p>First, HAVING should only be used with a corresponding GROUP BY. Your GROUP BY applies to the sub query (think of it as a table). I think you want a WHERE clause there.</p> <p>The self-join (*lesson_requests INNER JOIN (SELECT stuff from lesson_requests*) is kinda confusing too though I can see what you're trying to do (namely, get the record per student with the max request date).</p> <p>Finally, a varchar like "name" usually makes for a bad Primary Key. Consider creating some sort of integer student ID surrogate key and using that instead.</p> <p>Regardless, I think you want something like:</p> <pre><code>SELECT student_name, city, state, request_date, lat, lng, <<DISTANCE COLUMN>> AS distance, vendor.user_purchased FROM lesson_requests INNER JOIN ( SELECT student_id, MAX(request_date) AS max_request_date FROM lesson_requests WHERE <<COMPLEX CONDITION>> GROUP BY student_name ) AS recent_student_lesson_request ON lesson_requests.student_name = recent_student_lesson_request.student_name AND lesson_requests.request_date = recent_student_lesson_request.max_request_date LEFT JOIN vendor ON v.user_purchased = lesson_requests.student_name WHERE vendor.user_purchased <> 'abs_company' AND distance < BLAH; </code></pre> <p>This joins in the vendor table as an OPTIONAL table and assumes that user_purchased in the vendor table corresponds to the student_name. If that assumption doesn't apply, you need to figure out how vendor and lesson_requests is related and do the join using those columns.</p> <p>This will still print out a record if a given student_name never appears in the vendor table (because, obviously a student that IS NOT a vendor CAN NOT be the vendor 'abc_company')</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