SQL Filter with same ID but different values

Question

I have a query that shows the results like this:

CUSTOMER_ID	rented_car	BUY_dATE	SELL_dATE
001	FERRARI	01-03-2018	NULL
002	DODGE	01-03-2013	01-02-2015
001	FERRARI	01-01-2017	02-02-2020
002	DODGE	01-03-2019	NULL
002	DODGE	01-03-2019	01-03-2020
003	RENAULT	01-03-2019	01-03-2020

the query is

SELECT c.customer_id, s.name as rented_car,s.buy_date, s.sell_date
FROM company c
LEFT JOIN "user" u ON u.customer_id = c.customer_id
LEFT JOIN "cars" s ON s.customer_id = c.customer_id

I want to get all the customer's ID that doesn't have a car rented at the moment. If I use in the where clause a code like "where sell_date IS NOT NULL" it will show me customer ID 01,02,03 and the correct answer is only Customer ID 03.

Thanks in advance for the help

Answer 1

You need a query which groups the results by CUSTOMER_ID. You can try something like this :

SELECT c.customer_id
  FROM company c
  LEFT JOIN "user" u ON u.customer_id = c.customer_id
  LEFT JOIN "cars" s ON s.customer_id = c.customer_id
 GROUP BY c.customer_id
HAVING NOT bool_or(daterange(s.buy_date, s.sell_date) @> Now() :: date)

see the test result in dbfiddle.