How do i filter all results of a one to many-CodePudding

Imagine the following tables:

CREATE TABLE item (code string);
CREATE TABLE item_event (id int, item_code string, event int, date smalldatetime);

item:
code
item1
item2
item3
item4

item_event:
id | item_code  | event | date
1  |  item1     | 123   | 2021-05-04 05:50
2  |  item1     | 456   | 2021-05-04 06:50
3  |  item2     | 123   | 2021-05-04 05:50
4  |  item2     | 678   | 2021-05-04 08:50
5  |  item3     | 456   | 2021-05-04 05:50
6  |  item3     | 890   | 2021-05-04 09:50
7  |  item4     | 123   | 2021-05-04 11:50
8  |  item4     | 456   | 2021-05-04 20:50
9  |  item4     | 890   | 2021-05-04 01:50

How do I select the items that have no 456 event or a 456 event whose date is after a 890 event if it has one?

So in this case it would return item2 and item4.

Can't figure out how to do it in SQL. Everything i have tried just filters out the individual event instead of looking at a set "grouped by item_code."

CodePudding user response：

Using aggregation we can try:

WITH cte AS (
    SELECT item_code
    FROM item_event
    GROUP BY item_code
    HAVING COUNT(CASE WHEN event = 456 THEN 1 END) = 0 OR
           MIN(CASE WHEN event = 890 THEN date END) <
           MIN(CASE WHEN event = 456 THEN date END)
)

SELECT *
FROM item_event
WHERE item_code IN (SELECT item_code FROM cte);

Demo

The logic of the above HAVING clause is to retain any item_code set of records having no 456 event at all, or any item_code where its earliest 890 event occurs before the earliest 456 event. In this case, that would mean that every 456 event had at least one earlier prior 890 event.

CodePudding user response：

There are 2 methods of doing this, though which depends on what question you're trying to answer. The first is with a NOT EXISTS, which would likely be better for your first scenario (no 456 events). Which would look like this:

SELECT Code
FROM dbo.Item I
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.Item_Event IE
                  WHERE IE.Item_Code = I.Code
                    AND IE.Event = 456);

The latter would be to us a HAVING with conditional aggregation, which is likely more suited to your second requirement, due to the need to compare 2 different events:

SELECT I.Code
FROM dbo.Item
     JOIN dbo.Item_Event IE ON I.Code = IE.Item_Code --This *might* need to be a LEFT JOIN
GROUP BY I.Code
HAVING COUNT(CASE WHEN Event = 456 THEN 1 END) = 0
    OR MIN(CASE WHEN Event = 890 THEN date END) < MIN(CASE WHEN Event = 456 THEN Date END);

CodePudding user response：

This looks like a typical 'nested negation' exercise. The problem becomes easier to express in SQL when you rephrase it as follows:

Give me every item that does not have
- a 456 event that is not accompanied by
  - a 890 event with a date before 456's

Each indentation becomes a subquery. The negations are expressed by NOT IN or NOT EXISTS.

DROP TABLE item IF EXISTS 
DROP TABLE item_event IF EXISTS 
CREATE TABLE item (code string)
CREATE TABLE item_event (id int, item_code string, event int, date smalldatetime)
INSERT INTO item VALUES ('item1'), ('item2'), ('item3'), ('item4')
INSERT INTO item_event VALUES
    (1, 'item1', 123, '2021-05-04 05:50'),
    (2, 'item1', 456, '2021-05-04 06:50'),
    (3, 'item2', 123, '2021-05-04 05:50'),
    (4, 'item2', 678, '2021-05-04 08:50'),
    (5, 'item3', 456, '2021-05-04 05:50'),
    (6, 'item3', 890, '2021-05-04 09:50'),
    (7, 'item4', 123, '2021-05-04 11:50'),
    (8, 'item4', 456, '2021-05-04 20:50'),
    (9, 'item4', 890, '2021-05-04 01:50')

SELECT *
FROM item
WHERE code NOT IN (
    SELECT ie456.item_code
    FROM item_events ie456
    WHERE ie456.event = 456
    AND NOT EXISTS (
        SELECT *
        FROM item_events ie890
        WHERE ie890.item_code = ie456.item_code
        AND ie890.event = 890
        AND ie890.date >= ie456.date
    )
)

Output:

item2
item4