CODEWITHSUNDEEP
mysql
kielou
kielou

Reputation: 437

Having trouble with except query

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

Upvotes: 0

Views: 40

Answers (1)

juergen d
juergen d

Reputation: 204766

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

Upvotes: 3

Related Questions