Find all books where availability is = 0 or unknown

Question

I have a table Books which has many properties. Properties are stored as key and value.

So if Books are:

1  LOTR
2  Harry Potter 1
3  Harry Potter 2

And properties are

id  book_id  key        value
1   1        available  0
2   2        available  10
3   2        author     Rowling
4   3        author     Rowling

I'd like to get the results as:

1  LOTR
3  Harry Potter 2

since Book id 1 has 0 availability, and 2 has 10, and 3 does not have any availability info.

I know I can work with anti join, but I am not sure how to use it. I'm kind of new to anti joins.

Any help is appreciated.

Answer 1

I'm not 100% sure I'm understanding your question, but assuming you want to return all books that have no availability in the properties table, then here's one option using an outer join:

select b.*
from books b
   left join properties p on b.id = p.book_id and p.key = 'available' and p.value > 0
where p.id is null

Depending on your database, you may need to cast the value column in the join.