How to select rows from table where group does not contain value

I have a table that contains the following fields:

Thedate,Thetime,Course,OR,L1R,L2R,L3R

I would like to only return values if a group (Thetime) does not contain the value “0” in L3Ra column.

I have tried the following statement but it only removes those values that contain the “0” where I would like to remove the whole group

  SELECT *
FROM orclass t
WHERE t.l3r NOT IN
   (SELECT s.l3r  
    FROM  orclass s
    WHERE s.l3r = '0')
    and Thedate = "2019-01-18"
ORDER BY t.Thetime

What i would like to do is remove any groups (Thetime) that contain a “0” in the L3R column.

Thetime L1R L2R L3R
  12:00  1     2   1
  12:00  1     2   0
  13:00  1     1   1
  13:00  2     0   8
  14:00  2     4   9
  14:00  0     3   6

Would become:

Thetime L1R L2R L3R
  13:00  1     1   1
  13:00  2     0   8
  14:00  2     4   9
  14:00  0     3   6

because the group 12:00 contains a “0” in LR3

Answer

You could use this:

SELECT *
FROM orclass o
WHERE Thedate = "2019-01-18"
    AND NOT EXISTS
    (
        SELECT 1
        FROM  orclass o1
        WHERE o1.Thedate = o.Thedate 
            AND o1.Thetime = o.Thetime 
            AND o1.lr3 = '0'
    )
ORDER BY Thetime;

Advertisement

Answer