StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><em>Please note... I'm shooting from the hip. You may find small bugs or logical errors in the code below. I am also not a MySQL expert, so I might have made an assumption which is true for most DBMSes, but not true for MySQL specifically. Take with a grain of salt and always test thoroughly.</em></p> <p>I'll take your question as an example of how to think through your requirements:</p> <blockquote> <p><em><strong>How do I create a query that returns, for a specified racedate,</em></strong></p> </blockquote> <p>Let's start here. You need all races for a specified race date, which is going to involve selecting rows from your <code>Races</code> table:</p> <pre><code>SELECT * FROM Races WHERE racedate = '2010-10-01' </code></pre> <blockquote> <p><em><strong>the racetime/course/horse</em></strong></p> </blockquote> <p>This is simply the data you want to select, but not all of it is in your <code>Races</code> table. You can get the <code>racetime</code> and <code>course</code> from <code>Races</code>, but the <code>horse</code> must come from runners. So the next step is to join your <code>Races</code> table to the <code>Runners</code> table:</p> <pre><code>SELECT Races.racetime , Races.course , Runners.horse FROM Races , Runners WHERE racedate = '2010-10-01' AND Runners.raceid = Races.raceid </code></pre> <blockquote> <p><em><strong>where a jockey has only one ride at that course</em></strong></p> </blockquote> <p>This one is a bit trickier. What you need to do at this point is put your current query on hold and start a new query to check to see how many races a particular jockey ran at a particular race track on a particular day. We can get <code>jockey</code> from the <code>Runners</code> table, but we need to join back to the <code>Races</code> table to get the <code>racedate</code> and <code>course</code>:</p> <pre><code>SELECT COUNT(*) FROM Runners , Races WHERE Races.raceid = Runners.raceid AND Runners.jockey = 'jockeyName' AND Races.course = 'courseName' AND Races.racedate = '2010-10-01' </code></pre> <p>We now have a query which will give us the number of races a jockey made. We can now incorporate this back into our original query as a subquery, getting the <code>jockey</code>, <code>course</code> and <code>racedate</code> from the parent query (I use aliases on the subquery tables so as not to confuse those tables with the parent tables, since they are the same):</p> <pre><code>SELECT Races.racetime , Races.course , Runners.horse FROM Races , Runners WHERE racedate = '2010-10-01' AND Runners.raceid = Races.raceid AND ( SELECT COUNT(*) FROM Runners AS sub_run , Races AS sub_race WHERE sub_race.raceid = sub_run.raceid AND sub_run.jockey = Runners.jockey AND sub_race.course = Races.course AND sub_race.racedate = Races.racedate ) = 1 </code></pre> <blockquote> <p><em><strong>and that horse is the trainer's only horse at that course</em></strong></p> </blockquote> <p>This is going to be yet another subquery, as you might guess at this point:</p> <pre><code>SELECT COUNT(DISTINCT Runners.horsename) FROM Runners , Races WHERE Races.raceid = Runners.raceid AND Runners.trainer = 'trainerName' AND Races.course = 'courseName' AND Races.racedate = '2010-10-01' </code></pre> <p>This query includes "COUNT(DISTINCT ...)" to ensure that we are only counting unique horse names and not the same horse multiple times (multiple races, etc).</p> <pre><code>SELECT Races.racetime , Races.course , Runners.horse FROM Races , Runners WHERE Races.racedate = '2010-10-01' AND Runners.raceid = Races.raceid AND ( SELECT COUNT(*) FROM Runners AS sub_run , Races AS sub_race WHERE sub_race.raceid = sub_run.raceid AND sub_run.jockey = Runners.jockey AND sub_race.course = Races.course AND sub_race.racedate = Races.racedate ) = 1 AND ( SELECT COUNT(DISTINCT sub_run.horsename) FROM Runners AS sub_run , Races AS sub_race WHERE sub_race.raceid = sub_run.raceid AND sub_run.trainer = Runners.trainer AND sub_race.course = Races.course AND sub_race.racedate = Races.racedate ) = 1 </code></pre> <p>(You can reuse the subquery table aliases in subsequent subqueries - because each subquery executes in a different context, the DBMS will not be confused)</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