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

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?

CodePudding user response：

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

View on DB Fiddle

CodePudding user response：

You can try to use the aggregate window function in subquery

SELECT *
FROM (
 SELECT *,
       MAX(CASE WHEN available = 'N' THEN 1 END) 
        OVER(PARTITION BY name) is_available
 FROM T
) t1
WHERE is_available IS NULL AND 
date BETWEEN '2019-10-11' AND '2019-10-13'