除了查询以外遇到问题

Question

我有两个表（书籍和借阅）。

我想从表Books中选择全部。 但首先它会检查BookID是否存在于表Borrowed中，如果它存在且其Status = 1，它将不会被包含在select *语句中。

我都试过，但它不工作

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

请帮助我。这是我的表

书籍：

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

借：

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

我的目标输出：

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

Answer 1

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

见this great explanation of joins