CODEWITHSUNDEEP
sqldatabaseoracle-database
user7002207
user7002207

Reputation: 457

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

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.

Upvotes: 1

Views: 61

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146499

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

Upvotes: 1

user5683823
user5683823

Reputation:

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.

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

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
);

Upvotes: 2

Related Questions