need help with mysql for book system - problem with where clause

hello all im developing new project for publisher ...this project for develop system saved all books for that publisher ............... we have 3 table books author coauthor

problem case: ieach book have 1 main author thats author could replay in alot of开发者_开发技巧 books and ieach book in some cases could have co-author this co author could be 1 or 2 or 3 or 12 note: this co author is already saved in author table

realtionship: one to many between authors and books

many to many between coauthor and books and authors

table #1 authors table => table hold all authors id - author_name

table #2 books table => table hold all books id- title - author_id - publishing_year

table #3 co_authors table => hold all item which have alot of co author item_id and authors_id

now how i can retrive all books for specific author if he is main author or co author

There're two ways you can go about this... One is to use a UNION, so something like:

SELECT id FROM books WHERE author_id = ?
UNION ALL
SELECT item_id FROM co_authors WHERE authors_id = ?

Another way you could solve this is to rework your database structure such that there is no author_id in the books table and instead you have a flag in co_authors called main_author or some such. Considering you may have books where it's unclear who the main author is, this may make more sense...

You can do something like this :

SELECT DISTINCT books.*, authors.*
FROM books
LEFT JOIN co_authors ON co_authors.item_id = books.id
INNER JOIN authors ON 
        authors.id = books.author_id OR 
        authors.id = co_authors.author_id
WHERE authors.id = ID

The where clause can also be written where authors.name = 'NAME' if you need to search by name and not id.

EDIT: added distinct to avoid multiple rows for the same book.