Because of the way I import and tag my books, I occasionally want to go through the database and find the authors with a certain tag on any of their books, and then then find all books by those authors. The simple filtering facility provided by calibre don't allow for such things, but it is (relatively) straightforward in SQL. I came up with the following query which gives me what I want, but it would be nice to be able to enter it into calibre somehow so that I can interact directly with it's results.
Code:
SELECT b.title,a.name FROM(
SELECT DISTINCT ba.author AS author
FROM tags AS t JOIN books_tags_link AS bt ON t.id=bt.tag
JOIN books AS b ON bt.book=b.id
JOIN books_authors_link AS ba ON b.id=ba.book
WHERE t.name="my tag"
) AS x
JOIN books_authors_link AS ba ON x.author=ba.author
JOIN books AS b ON ba.book=b.id
JOIN authors AS a ON ba.author=a.id
ORDER BY b.author_sort
Possibly one way to do it would be to provide an SQL entry box which provides a fixed 'SELECT DISTINCT books.id FROM' at the start, allowing you to then enter any arbitrary query, and thus return a list of book IDs, which can then be used to populate the table as usual.
I know it's a fairly specialized desire, but I suspect there are a few people around who would use it if it were available!