Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Please note, I originally asked this question on Stack Overflow however all the solutions presented there required loading the entire table into memory then filter on a self-JOIN. That solution won't work with tables of this size, so I realize that this is not a simple SQL query for Stack Overflow but rather an issue for a professional DBA with large-table experience.

share|improve this question
    
Can you create an example on www.sqlfiddle.com with example data? –  Raymond Nijland Jan 29 at 21:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.