0

I got two tables (Books and Borrowed).

I want to select all from table Books. But first it will check if the BookID exists in the table Borrowed and if it does exists and its Status= 1, it wont be included from the select * statement.

I have tried this but it doesn't work

select * from Books
except
select `BookdID` where `Status`=1

Please help me. This are my table

Books : 

 | BookID     | Title       | Author       |
 |:-----------|------------:|:------------:|
 | 1          | The Stars   | William Evan |   
 | 2          | Science 3   | Vhong Navaro |
 | 3          | Let It Burn | Jun Ryl      |
 | 4          | Sky Below   | Jason Midfle |
 | 5          | Wish It     | Shan Uddle   |

Borrowed :

 | BookID     | BorrowerID  | Status       |
 |:-----------|------------:|:------------:|
 | 6          | 12          | 0            |   
 | 5          | 4           | 0            |
 | 2          | 3           | 1            |
 | 18         | 6           | 1            |
 | 5          | 3           | 0            |

My goal output:

| BookID     | Title       | Author       |
|:-----------|------------:|:------------:|
| 1          | The Stars   | William Evan |   
| 3          | Let It Burn | Jun Ryl      |
| 4          | Sky Below   | Jason Midfle |
| 5          | Wish It     | Shan Uddle   |
  *(BookID 2 is is not shown because in the table `Borrowed`, BookID 2's `Status` = 1)*
kielou
  • 437
  • 3
  • 18
  • Possible duplicate of [Error when using except in a query](http://stackoverflow.com/questions/16092353/error-when-using-except-in-a-query) – Armfoot Oct 05 '15 at 14:25

1 Answers1

3
select b1.* 
from Books b1
left join Borrowed b2 on b1.bookid = b2.bookid
                     and b2.`Status` = 1
where b2.bookid is null

See this great explanation of joins

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • It says `Column 'Status' in on clause is ambiguous`, What does it mean? – kielou Oct 05 '15 at 14:07
  • It means both tables have a `status` column and the DB does not know which one to take. I updated the query and added the table name to the `status` column. – juergen d Oct 05 '15 at 14:10
  • @kielou I recommend you to read the page juergen provided and then to look directly into [MySQL's `JOIN` documentation](https://dev.mysql.com/doc/refman/5.0/en/join.html). Only by understanding the way things work, you may be able to solve similar problems by yourself in the future. Actually your question has been asked before and [the answer](http://stackoverflow.com/a/16092507/1326147) also consisted of a `LEFT JOIN`, since `EXCEPT` does not exist in MySQL. – Armfoot Oct 05 '15 at 14:24
  • Thank you sir. I found the problem and it works. Thank you so much. – kielou Oct 05 '15 at 14:30
  • @Armfoot Im reading while waiting for his response, and i think i can now solve the other problems that i have now thanks to juergen d.. – kielou Oct 05 '15 at 14:32