A Question of Syntax
Tricky problem of the day for me: finding what's missing in an SQL Table. Here's the setup: The table items contains two string columns, one which contains the name of the series which the book belongs to (e.g. "Fellowship of the Ring" is part of "Lord of the Rings") and another string field which hold the position number (e.g. 1 in the previous example). Quite why these are not a foreign key to a series table, or why they're not even indexed based on an integer eludes me, and I something I intend to ask the original designers at knife point. Or possibly over afternoon tea1. There are a number of other fields, which contain data that is only supplementary to the data we need to obtain.
The task is to find all the books we are currently missing from series. This is the set of all books such that they are not in the table, and there is a book in the series which is in the database.
As there is no external source listing all the books we might potential have, we have to do this by inspection of the data that we do have. So, this is simplified as all books that are not in the database, but there are items in the database with a higher series number. Of course, this is difficult to implement in SQL. Another way of describing it is that it's all books where the the previous item does not exist in the database.
Unfortunately, I only realised this interpretation several hours after actually running a query; deriving a query from the earlier statement is left as an exercise to the reader. Go on, I dare you. I managed it on virtually no sleep.
So: the simpler query. Now just to write out the code, so that when this all has to be done again next year, I'm able to find it :)
SELECT `items`.`series`, `items`.`seriesNum`,
(
SELECT MAX(`items2`.`seriesNum`)
FROM `items` as `items2`
WHERE `items`.`series` = `items2`.`series`
AND `items2`.`seriesNum` < `items`.`seriesNum`)
) AS `previousSN`
FROM `items`
WHERE `items`.`seriesNum` <= `items2`.`seriesNum`; - 1 ↑ Which is dependent on my anger/stress levels, which are a function of how many hours sleep I've has in the last 48, and when I was last hugged. (11 and 48hrs, at time of writing)