Need help with some complex SQL query

Question

Hello guys,

I need some help with a complex SQL query. Here's my setup: there are two tables, one with authors, and another with books. The books table contains a field named "author" which holds author's id. If a book has more than one author, then the "author" field will contain all the authors ids separated by ";" (something like 2;34;234).

On the website I have to list all the books written by an author. If there's only one author, its simple, but how can I get those books where the author I'm interested in is the second or third author?

Many thanks.

Answer 1

If you can, change the schema. Try a lookup table with AuthorPosition, like so:

BookID  AuthorID  AuthorPosition
1       1         1
1       2         2
1       4         3
2       3         1
2       1         2

Then, if you need the second author, you can query for AuthorPosition = 2.

If you can't change the schema, you can get all the books (maybe even use a LIKE query, though I'm not sure how), then parse in business code the authors you want.

Answer 2

This is an example of bad table design. Ideally you would have 3 tables books, books2authors, authors.
books2authors would hold book_id and author_id and thus one book can have more authors and you can easily work with them in SQL.

If you can't change the design, then I would go with getting all the books, parsing the author_id field and if you want to get only the second author, issue extra query select * from authors where id = <second_author> to get the info you need.

Answer 3

If you can't change the schema to use an interesection table (e.g., BooksAuthors), then something like the following should work:

...
where AuthorID = '42' 
    or AuthorID = '42;%' 
    or AuthorID = '%;42;%' 
    or AuthorID = '%;42'

Only the first two of these clauses can make use of indexes though, so searches for authors other than the first will be slower.

Answer 4

Can you change the DB tables?

You should really put the book and author IDs in a table of their own (a join table).

This table (maybe called book_author) would just have the IDs for the Books and the Authors so you can perform queries like:

select a.* from book b, book_author ba, author a
where 
    b.id == ba.book_id
    and
    a.id == ba.author_id
    and 
    b.id = 123;

to return all authors for book with id 123.

Answer 5

I recommend you make a small change: replace the ; separator with , (comma). Then you can do this:

select b.*
from authors a
inner join books b on find_in_set(a.id, b.author) >0;

This is a good example of nightmares given by bad design. The query should work, but it will always perform full table scans (terribly slow when the tables grow bigger).