robin g robin g - 2 months ago 6
SQL Question

query a column that does not have the same value in another column

My Table

ItemCode ItemName Total
----------------------------------
A name1 5
A name1 5
A name2 10
B name1 10
B name2 25
B name1 30
C name2 5
C name1 30
C name1 20


i want to display all
itemcode
A
and
B
that does not have the same value in column
Total


My expected Result

ItemCode ItemName Total
----------------------------------------
A name1 5
A name1 5
B name2 25
B name1 30


I already asked this question but this time my question is much clearer than my last quesiton. I think 1 solution for this is a self join but i cant figure it out. any help would mean a lot to me thanks!

Answer

Assuming that you need the rows for which does not exist another row of a different itemCode with the same Total, and assuming that ItemCode is always not null, a simple solution can be this:

with test(ItemCode, ItemName, Total) as 
(
    select 'A', 'name1', 5  from dual union all
    select 'A', 'name1', 5  from dual union all
    select 'A', 'name2', 10 from dual union all
    select 'B', 'name1', 10 from dual union all
    select 'B', 'name2', 25 from dual union all
    select 'B', 'name1', 30 from dual union all
    select 'C', 'name2', 5  from dual union all
    select 'C', 'name1', 30 from dual union all
    select 'C', 'name1', 20 from dual
)
select *
from test t1
where ItemCode in ('A', 'B')
  and not exists (
                   select 1
                   from test t2
                   where t1.total    =  t2.total
                     and t1.itemCode != t2.itemCode
                     and ItemCode in ('A', 'B')
                 )

The following is faster, but less readable:

select ItemCode, ItemName, Total
from (
        select ItemCode, ItemName, Total, count(distinct ItemCode) over (partition by Total) as itemCount
        from test
        where ItemCode in ('A', 'B')
     )
where itemCount = 1  
Comments