I have orders table that have delete marker column date_deleted which value is null or date

I have query to select the fully deleted orders with group by order_number

SELECT order_number, 
       date_deleted 
FROM orders 
WHERE date_deleted IS NOT NULL 
GROUP BY order_number

It give me this result

Which indicate that the whole order is deleted but is not.

Is there a way to get null when not all of them is null?

CodePudding user response：

You can use a case expression to return the latest deleted where all records are deleted, and where any are NULL the expression will return NULL:

SELECT order_number, 
        CASE WHEN COUNT(*) = COUNT(date_deleted) THEN MAX(date_Deleted) END AS date_deleted
FROM orders 
GROUP BY order_number;

You may also wish to return the earliest deleted date with MIN(date_deleted), the point is though, if you group by order_number you can only return one date_deleted and you have to tell the query engine which one you want to return by using some kind of additional logic like MIN or MAX.

CodePudding user response：

This will only return fully deleted orders.

If you join on itself and check for NULL values(not deleted rows/parts) in the join, than set "if the parts IS NULL" it only included orders where there are no NULL values in the date_deleted part(and thus fully deleted).

This also does not use COUNT or other groupings that could slow the query down so should be fast and readable.

SELECT 
    order_number, 
    date_deleted 
FROM orders 
    LEFT JOIN orders openOrderParts ON openOrderParts.order_number = openOrderParts.order_number 
        AND openOrderParts.date_deleted IS NULL
WHERE date_deleted IS NOT NULL AND openOrderParts IS NULL
GROUP BY order_number

But you have to remember, it is grouped by order number, do "date_deleted" could contain more than ne value but you are just showing one. you could consider using GROUP_CONCAT there to optionally show all if any.