I have a quite hard time with the following queries. I have the following tables:

1. Book table Contains all the information including price. This table contain a row "available" that is default true. when the "available" row is true, the book is not belong to any promotion and the price is normal.

however, when the admin promote a book, the "available" row will be false until the remove_time will activate the automatic function to remove the promotion.

2. Book Image table Contains all the images links and the book id assigned of individual books. Here is possible by requirement not to have any book image assigned.

3. Promotion table This table contain a start date (default), end date, new price, and book ID. The main thing is that when the server reads the end date, the deletion of the product from the promotion table will automatically be removed. All the books that are found in the promoted table have the "available" row value false.

I need two queries, or JS functions to select all the books promoted and unpromoted while taking in consideration the new_price from the promoted table (if the bookID is inside the table) and also the book images from the table.

I tried this, but when the book contains more images, I get duplicate books for each images,....

select * from magicpapers.books 
    right join magicpapers.bookimages
    ON books.bookID = bookimages.bookID

Thanks, Daniel