SQL Filter with same ID but different values-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

You can use window function to first determine which record to pick for each of the customers and then apply your where clause. Here is an example

WITH cte AS 
(
  SELECT c.customer_id, 
         s.name as rented_car,
         s.buy_date, 
         s.sell_date,
         ROW_NUMBER() OVER(partition BY c.customer_id ORDER BY s.buy_date DESC) AS rn
  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
)
SELECT customer_id, 
       rented_car,
       buy_date, 
       sell_date
FROM cte
WHERE rn = 1
      AND sell_date IS NOT NULL