Amit Amit - 1 year ago 67
SQL Question

Find all books where availability is = 0 or unknown

I have a table

which has many
. Properties are stored as key and value.

So if Books are:

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:

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 Source

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 = p.book_id and p.key = 'available' and p.value > 0
where is null

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download