Quote
I am doing my best to learn mysql but I still have a lot to learn when queries involve loops or complex syntax.
As far as the database goes, stop thinking about "Loops" and start thinking about "Sets". Any query you construct will locate a Set of rows and return them to you.
If you then want to loop through those rows returned in your application code that's fine, but in the database, loops
will not help you.
Complex syntax [all too] often means that your data is poorly structured and you're having to do "clever" things to work around that deficiency.
Quote
First question: how could I combine these two queries into one and get the total number of missing predictions for each user?
Possibly, but not easily, by which I mean the query will get more complex, which is a Bad Thing; not for MySQL, which will cope with practically anything, but will rapidly become unreadable and, therefore, unmaintainable.
Again, thinking about Sets of data rows, the first query returns a set of all games since the start of the season, the second a set of predictions made by a particular user. Different sets, different queries.
That said, you might be able to determine, within a Set of every game played, whether or not the user has made a prediction for that game, and to "total up" the result:
-- Air-Code Warning
select count( g.id ) games
, count( p.userId ) predictions
from table-1-name g
left join table-2-name p
on g.id = p.gameId
and p.userId = ?
where g.SeasonID = ?
and ( g.homeTeamScore > 0 or g.awayTeamScore > 0 )
order by g.gametime desc
See what I mean about things getting complex?
Is that any easier to read than the two, simple queries that you had before? Not really. And, when you come back to this query after six months working on something else, clarity is really,
really important.
Deciding what was "consecutive" with what would require a reference table listing all the possible week values, which you would join to the prediction entries made by the user. Your application code would then need to trawl through that Set and find the gaps; there's no easy way for MySql to do this because it has no concept of "previous" or "next" rows within a Set. You
can do row-by-row processing within a Procedure
but you should view this as a
last resort. Looping procedure code creates single-threaded bottlenecks which mean poor application performance, and there's nothing you can do to "tune" these, short of re-writing them in a "better" way (which
usually involves using the power of MySQL; simple SQL statements, working with Sets of rows).
Regards, Phill W.