Question: Query to omit an entire group which has one record with a certain value in an attribute

Question

Query to omit an entire group which has one record with a certain value in an attribute

Answers 3
Added at 2016-12-28 14:12
Tags
Question

I need to create a personal table based on particular criteria from another table. The records associated with the data are grouped together (based on distance from each other). I have another field that is populated with a particular code. The result of the query should only include all attributes from all groups that DO NOT have a certain codes (in this case 2 and 3):

|groupid |  id   |  code  | stuff |
----------------------------------|
|    a   |   1   |   1    |  data |
|    a   |   2   |   1    |  data |
|    b   |   3   |   1    |  data |
|    b   |   4   |   2    |  data |
|    c   |   5   |   1    |  data |
|    c   |   6   |   3    |  data |
|    d   |   7   |   2    |  data |
|    d   |   8   |   4    |  data |
|    e   |   9   |   4    |  data |
|    e   |   10  |   4    |  data |
-----------------------------------

In this case I need to create a personal table that contains all the records and attributes where the results are as follows:

|groupid |  id   |  code  | stuff |
----------------------------------|
|    a   |   1   |   1    |  data |
|    a   |   2   |   1    |  data |
|    e   |   9   |   4    |  data |
|    e   |   10  |   4    |  data |
-----------------------------------

Because codes 2 and 3 were unreliable for the study, the whole group that contains any of these values cannot be analyzed. The query should be a select * as I need all the attributes (there are more than 4). Thank you.

Answers
nr: #1 dodano: 2016-12-28 14:12
select *
from your_table t1
where not exists (
    select 1 from your_table t2
    where t1.group = t2.group
    and t2.code in (2, 3)  -- exclusion list here
);
nr: #2 dodano: 2016-12-28 14:12

Pretty much as you describe it in English

Select * from table a
Where Not exists
    (Select * from table 
     where groupid = a.groupId
         and code in (2,3)) 

test case:

declare @t table 
(groupid char(1) not null,
 id int not null, code int not null, 
 stff varchar(10) not null)
insert @t(groupid, id, code, stff)values
('a',  1, 1, 'data'),
('a',  2, 1, 'data'), 
('b',  3, 1, 'data'), 
('b',  4, 2, 'data'), 
('c',  5, 1, 'data'), 
('c',  6, 3, 'data'),
('d',  7, 2, 'data'),
('d',  8, 4, 'data'),
('e',  9, 4, 'data'),
('e', 10, 4, 'data')
select * from @t

Select * from @t a
Where Not exists
    (Select * from @t 
     where groupid = a.groupId
         and code in (2,3)) 

results:

  a   1  1  data 
  a   2  1  data 
  e   9  4  data 
  e  10  4  data
nr: #3 dodano: 2016-12-28 15:12

This can be done with analytic functions, so that the base table is read just once - resulting in better performance. This is pretty much what analytic functions were created for.

If you have too many columns and don't want to type their names twice (although that is the "best practice"), you may select * in the outer query if you don't mind keeping the ct column (where all values will be 0), and in the inner query you may select <table_name>.*, count(....).... In the inner query you must qualify * with the table name, since you are also selecting an additional "column", ct.

with
     test_data ( groupid, id, code, stuff ) as (
       select 'a',  1, 1, 'data' from dual union all
       select 'a',  2, 1, 'data' from dual union all
       select 'b',  3, 1, 'data' from dual union all
       select 'b',  4, 2, 'data' from dual union all
       select 'c',  5, 1, 'data' from dual union all
       select 'c',  6, 3, 'data' from dual union all
       select 'd',  7, 2, 'data' from dual union all
       select 'd',  8, 4, 'data' from dual union all
       select 'e',  9, 4, 'data' from dual union all
       select 'e', 10, 4, 'data' from dual
    )
-- end of test data; the solution (SQL query) begins below this line
select groupid, id, code, stuff
from   ( select groupid, id, code, stuff,
                count(case when code in (2, 3) then 1 end)
                     over (partition by groupid) as ct
         from   test_data
       )
where  ct = 0
order by groupid, id   --  order by is optional
;

GROUPID   ID   CODE STUFF
------- ---- ------ -----
a          1      1 data
a          2      1 data
e          9      4 data
e         10      4 data

4 rows selected.
Source Show
◀ Wstecz