Amit Amit - 5 months ago 16
SQL Question

Find all books where availability is = 0 or unknown

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

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.

Comments