Need help with some complex SQL query

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, 开发者_Go百科its simple, but how can I get those books where the author I'm interested in is the second or third author?

Many thanks.

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.

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.

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.

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.

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).