Home Return rows if and only if all rows with same name are available during date range

Questions

Return rows if and only if all rows with same name are available during date range

April 9, 2022

I have the following table (that I can’t change)

id	name	date	available
1	Book_1	2019-10-11	Y
2	Book_1	2019-10-12	Y
3	Book_1	2019-10-13	Y
4	Book_1	2019-10-14	Y
5	Book_2	2019-10-11	Y
6	Book_2	2019-10-12	Y
7	Book_2	2019-10-13	Y
8	Book_3	2019-10-11	Y
9	Book_3	2019-10-12	N
10	Book_3	2019-10-13	Y

User wants to search for books that he/she can borrow from 2019-10-11 to 2019-10-13, and I need to return the following table

id	name	date	available
1	Book_1	2019-10-11	Y
2	Book_1	2019-10-12	Y
3	Book_1	2019-10-13	Y
5	Book_2	2019-10-11	Y
6	Book_2	2019-10-12	Y
7	Book_2	2019-10-13	Y

Notice that Book_3 is unavailable on 2019-10-12 so I cant’ return it. Is possible with SQL?

>Solution :

You can use a subquery with BOOL_AND to check that available is true for all dates for a book:

SELECT *
FROM books
WHERE date BETWEEN '2019-10-11' AND '2019-10-13'
  AND name IN (
  SELECT name
  FROM books
  WHERE date BETWEEN '2019-10-11' AND '2019-10-13'
  GROUP BY name
  HAVING BOOL_AND(available)  -- or BOOL_AND(available = 'Y') if the column is not a BOOLEAN
);

id	name	date	available
1	Book_1	2019-10-11T00:00:00.000Z	true
2	Book_1	2019-10-12T00:00:00.000Z	true
3	Book_1	2019-10-13T00:00:00.000Z	true
5	Book_2	2019-10-11T00:00:00.000Z	true
6	Book_2	2019-10-12T00:00:00.000Z	true
7	Book_2	2019-10-13T00:00:00.000Z	true