@sbin:
As I promised you, a raw SQL Query for use in the MCS plug-in to give you the books that you requested is shown below. You will have to change the "10" (as in custom_column_10) to whatever the correct number is per the ToolTips in the MCS SQL Query Tab shown when hovering your mouse outside of the input area. See the image in my prior post to see the list of custom columns for the current Library that appears when you hover in the grey area.
DaltonST
Code:
SELECT id
FROM books
WHERE
(id IN
(SELECT book FROM custom_column_10
WHERE custom_column_10.value = 1)
AND
id IN
(SELECT book FROM books_series_link
WHERE books_series_link.series IN
(SELECT series FROM books_series_link
WHERE books_series_link.book <> books.id
AND books_series_link.book NOT IN
(SELECT book FROM custom_column_10
WHERE custom_column_10.value = 1))))
OR
(id IN (SELECT book FROM custom_column_10 WHERE custom_column_10.value = 0))
OR
(id NOT IN (SELECT book FROM custom_column_10))
/* Explanation:
Custom Column 10 uses non-normalized table custom_column_10, which in this scenario is the boolean (Yes/No) datatype meaning "Has Been Read Already".
[1] the top part retrieves all books that have already been read but also simultaneously have a series that is found for other books that have not already been read.
[2] the middle part retrieves all books that are explicitly marked as not already having been read.
[3] the bottom part retrieves all books that are not marked one way or the other as having been read (i.e., are undefined for that custom column, meaning that they are not even in the table).
*\