Select Foo object without any specific Bar events

Question

Consider two tables:

Foo:
  id INT,
  name VARCHAR

Bar:
  id INT,
  foo_id INT REFERENCES Foo(id),
  event_type VARCHAR DEFAULT NULL,
  event_duration INT DEFAULT NULL

Each Foo item can have multiple Bar events. How to query for Foo items which do not have any Bar events that meet either of the following conditions:

1. event_type that is not one of the following values: 'miss', 'scratch', 'scrape'
2. event_duration that is not null

For instance, consider:

Foo id=1:
    event_type: hit       | event_duration: NULL
    event_type: poke      | event_duration: NULL
    event_type: capture   | event_duration: NULL

Foo id=2:
    event_type: hit       | event_duration: 2
    event_type: poke      | event_duration: NULL
    event_type: capture   | event_duration: NULL

Foo id=3:
    event_type: miss      | event_duration: NULL
    event_type: poke      | event_duration: NULL
    event_type: capture   | event_duration: NULL

Foo id=4:
    event_type: strike    | event_duration: NULL
    event_type: hit       | event_duration: NULL
    event_type: land      | event_duration: NULL

Only the Foo items with id=1 and id=4 should be returned. The item with id=2 should not be returned as one of it's event_duration is not NULL. The item with id=3 should not be returned as one of it's event_type is miss (which is on the list of forbidden event_types).

I have tried various ideas from this terrific answer, which responds to a generalization of the situation which I had hoped to learn enough from in order to build this query. Alas, I have been unable to generalize the answer enough to resolve this issue. This is one example of an unworking query, there were quite a few other failed attempts:

SELECT
    f.name
FROM
    Foo f JOIN Bar b ON f.id = b.foo_id
GROUP BY
    b.event_type, b.event_duration
HAVING
    b.event_type not in ('miss', 'scratch', 'scrape')
  AND
    b.event_duration not null

Here is another unworking query:

SELECT
    f.name
FROM
    (
    SELECT
        f.name, b.event_duration
    FROM
        Foo f JOIN Bar b ON f.id = b.foo_id
    GROUP BY
        b.event_type
    HAVING
        b.event_type not in ('miss', 'scratch', 'scrape')
    )
GROUP BY
    b.event_duration
HAVING
    b.event_duration not null

There were lots of other unworking queries with several ideas about JOINs and subqueries. Note that the Foo table has almost 5 million rows, and the Bar table has almost 2 million rows. The tables are indexed on the relevant fields, but O(n^2) are simply impossible on these large tables.

Answer 1

You can use NOT EXISTS to get the results you wanted.

SELECT f.name
FROM foo f
WHERE NOT EXISTS (SELECT 1 FROM bar b
                  WHERE b.foo_id = f.id
                  AND (b.event_type IN ('miss','scratch','scrape')
                       OR b.event_duration IS NOT NULL)
                  )

Answer 2

Try this, even though it sounds simple, I think it's OK for your case.

select f1.id, f1.name from
Foo f1 
left join 

(
     select distinct f.id 
     from Foo f
     join Bar b on f.id = b.foo_id
     where
     ( b.event_type IN ('miss','scratch','scrape') ) OR ( b.event_duration IS NOT NULL )
) f2 on f1.id = f2.id 

WHERE
(f2.id is null)

Answer 3

You could create a "counter cache" field in the Foo table that will just hold the count of the associated Bar items.

I think your problem will be faster solved with two queries:

1. query to update the counter cache for all Foo items. Since this will look only at indexed columns it should be rather fast.

2. second query that will NOT do any joins but will just query the Foo table for the criteria you want and having the "counter cache" value of 0.

With a smart management of the "counter cache" column you may have to run the first query only once. (Of course your program would have to then maintain the "counter cache" column in sync).

Answer 4

I would try this one

SELECT DISTINCT f.Id
FROM Foo f
WHERE NOT EXIST (
                 SELECT DISTINCT b.foo_id
                 WHERE b.foo_id = f.Id
                       AND   (b.event_type IN ('miss','scratch','scrape')                     
                               OR b.event_duration IS NOT NULL)
                 )

You can also use Merge like this :

1. Create list of distinct Bar Ids that either have 'miss','scratch','scrape' in the event_type OR event_duration is not null
2. Merge Foo and Bar
3. Use the WHEN NOT MATCHED to find your result