|
@Tanjamuse:
Yes, but let me first say that this is worth doing only because you have 100,000 books...
See the attached image showing the "before" and "after" Calibre books selected.
To make this work for you, the following SQL shown in Steps 1-3 must be executed sequentially in a SQLite tool, such as SQLite Manager for Firefox or DBBrowser for SQLite.
To avoid any confusion with Standard Calibre column names, I added "FF" as appropriate.
The "30" is the table number for your FF Author ID custom column in my example, and the "31" is the table number for your FF Author Name custom column in my example. You must change the 30 and 31 to whatever your real numbers are.
-------
Step 1:
CREATE VIEW "__tanjamuse_ff_view_1" AS
SELECT book AS ff_author_id_book,
(SELECT value FROM custom_column_30 WHERE id = books_custom_column_30_link.value) AS ff_author_id,
(SELECT book FROM books_custom_column_31_link WHERE books_custom_column_31_link.book = books_custom_column_30_link.book) AS book,
(SELECT value FROM custom_column_31 WHERE id = (SELECT value FROM books_custom_column_31_link WHERE books_custom_column_31_link.book = books_custom_column_30_link.book)) AS ff_author_name
FROM books_custom_column_30_link
-------
Step 2:
CREATE VIEW "__tanjamuse_ff_view_2" AS
SELECT book,ff_author_id AS authorid,ff_author_name AS authorname
FROM __tanjamuse_ff_view_1
WHERE ((SELECT count(ff_author_id) FROM __tanjamuse_ff_view_1 WHERE ff_author_id = authorid ) > 1)
-------
Step 3:
CREATE VIEW "__tanjamuse_ff_view_3" AS
SELECT book as id, authorid as myauthorid,authorname as myauthorname
FROM __tanjamuse_ff_view_2
WHERE EXISTS
(SELECT book FROM __tanjamuse_ff_view_2 WHERE book <> id
AND authorname <> myauthorname
AND authorid = myauthorid )
-------
Step 4:
Using the MultiColumnSearch plug-in, execute this query in the Raw SQL Query Tab:
SELECT id FROM __tanjamuse_ff_view_3
Good luck.
DaltonST
|