SQL - How to group by filename and know when all rows in the group has status 0-CodePudding

I have a question how can I make a query that selects and groups all rows by filename and where all rows have a status of 0, SELECT if one has a row has a status other than 0 , the SELECT does not return any records from the file name grouping.

Input Table:

id(Key) filename - order - messagein - messageout - status
  1        a1         101     p1          p1           0
  2        a2         102     p2          p2           0
  3        a1         103     p3          p3           0 
  4        a2         104     p4          p4           1
  5        a2         105     p5          p5           0

Output Select

id filename - order - messagein - messageout - status
 1    a1         101     p1          p1           0
 3    a1         103     p3          p3           0

the select skips the entire group to which the filename a2 belongs because one of the rows in its group has a status = 1

Any help would be appreciated. Thank you.

CodePudding user response：

That's a typical use case for an IN clause.

SELECT id, filename, "order", messagein, messageout, status
FROM yourtable 
WHERE filename NOT IN (SELECT filename FROM yourtable WHERE status != 0);

Please do not use SQL key words as column names. Here you need to quote your column name "order" because this is a SQL key word. To avoid issues when someone forgets this quoting, you should rename the column. Furthermore you should pay attention in case the column "status" is nullable since NULL will not be considered as != 0. If you want to do not accept NULL values, too, you need to replace them by a value != 0, as example using COALESCE:

SELECT id, filename, "order", messagein, messageout, status
FROM yourtable 
WHERE filename NOT IN (SELECT filename FROM yourtable WHERE COALESCE(status,1) != 0);

CodePudding user response：

your data

CREATE TABLE mytable(
   id         INTEGER  NOT NULL 
  ,filename   VARCHAR(20) NOT NULL
  ,order1      INTEGER  NOT NULL
  ,messagein  VARCHAR(20) NOT NULL
  ,messageout VARCHAR(20) NOT NULL
  ,status     INTEGER  NOT NULL
);
INSERT INTO mytable
(id,filename,order1,messagein,messageout,status) VALUES 
(1,'a1',101,'p1','p1',0),
(2,'a2',102,'p2','p2',0),
(3,'a1',103,'p3','p3',0),
(4,'a2',104,'p4','p4',1),
(5,'a2',105,'p5','p5',0);

use NOT IN

SELECT *
FROM mytable
WHERE filename NOT IN
      ( SELECT filename
        FROM mytable where status=1
      ) and status=0

dbfiddle

CodePudding user response：

I think what you want is to group by filename AND status, so it will let differents status pass.

select * 
from test
where status = 0
group by filename, status;