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.

Why wouldn't you return `Harry Potter 1`? Not understanding your expected results... — sgeddes, Jun 27 '16 at 18:15
@sgeddes I used `Harry Potter 1` as the name of the first book. Also, what I'm after is, all books, where I know that they are all taken, or I am not sure of their availability. (This is a library management portal) — Amit, Jun 27 '16 at 18:18

score 3 · Accepted Answer · answered Jun 27 '16 at 18:20

3

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.

answered Jun 27 '16 at 18:20

sgeddes

62,311
6
61
83

@cableload i am looking for books where availability is either 0 or "null" – Amit Jun 27 '16 at 18:23
@Amit, yes, the where part `p.id is null` will take care of the null part – cableload Jun 27 '16 at 18:24
@cableload -- this would do just that. it will return null results as well as any that don't have value > 0, so 0 would get returned... – sgeddes Jun 27 '16 at 18:24

score 0 · Answer 2 · answered Jun 27 '16 at 18:20

0

Try this:

SELECT b.book_id, a.key, a.value
FROM Books AS B INNER JOIN AnotherTable AS A B.book_id = a.book_id
WHERE a.key = 'available' and (a.value = 0 OR a.value is null)

answered Jun 27 '16 at 18:20

Maciej Los

8,468
1
20
35

score 0 · Answer 3 · answered Jun 27 '16 at 18:23

0

SELECT book_id, title
FROM Books as B
WHERE B.book_id NOT IN (
                SELECT P.book_id 
                FROM properties as P
                WHERE P.key = available AND P.value <> 0)

Note that <> means NOT EQUAL

answered Jun 27 '16 at 18:23

Aroic

479
2
12

Find all books where availability is = 0 or unknown

3 Answers3