CODEWITHSUNDEEP
sqloracle-sqldeveloper
D.SQL
D.SQL

Reputation: 15

Where subquery for in and not in

I am trying to pull records that have a code 'H' but not both 'H' and 'J' For example:

ID      NBR TYPE_CODE
34134   123 H
34134   123 J
35555   231 H
35555   233 H
42012   421 H
42012   421 J

I want to only pull these records because they do not have a corresponding record with a 'J':

ID    NBR
35555 231
35555 233

For the query I ran, I am getting ALL of the above IDs and NBRs, since they do have an 'H' code. How do I get just the ones that ONLY have an 'H' code and not both?

Upvotes: 0

Views: 38

Answers (4)

Lee Mac
Lee Mac

Reputation: 16015

Using a join:

select t1.id, t1.nbr 
from table1 t1 inner join 
(
    select t.id 
    from table1 t 
    group by t.id 
    having max(t.type_code) = min(t.type_code)
) t2 on t1.id = t2.id
where t1.type_code = 'H'

Change table1 to the name of your table.

Upvotes: 0

forpas
forpas

Reputation: 164069

With not exists:

select t.ID, t.NBR from tablename t
where t.TYPE_CODE = 'H'
and not exists (
  select 1 from tablename where ID = t.ID AND TYPE_CODE = 'J'
)

Upvotes: 2

Alex Yu
Alex Yu

Reputation: 3537

You wrote that you need:

records that have a code 'H' but not both 'H' and 'J' 

SELECT ID, NBR FROM [your_table] T
    WHERE 
        EXISTS(SELECT * FROM [you_table] T2 
            WHERE 
                T1.ID = T2.ID AND T1.NBR=T2.ID 
                AND T2.TYPE_CODE = 'H')
        AND NOT EXISTS(
            SELECT * FROM [you_table] T2 
                WHERE 
                   T1.ID = T2.ID AND T1.NBR=T2.ID 
                   AND T2.TYPE_CODE = 'J')
        )

This must be your query

Upvotes: 0

level3looper
level3looper

Reputation: 1051

Using 'Not In'

Create Table #tbl
(
id Int,
nbr Int,
type_code Char(1)
)
Insert Into #tbl Values
(34134,123,'H'),    
(34134,123,'J'),
(35555,231,'H'), 
(35555,233,'H'),
(42012,421,'H'),
(42012,421,'J')

Query

Select * From #tbl Where id Not In 
(Select Distinct id From #tbl Where type_code = 'J')

Result

id      nbr type_code
35555   231 H
35555   233 H

Upvotes: 0

Related Questions