Sanjaya Weerakkody Sanjaya Weerakkody - 1 month ago 8
SQL Question

SQL Same Values but different secondary values

I've tried this for hours and different forums but cant get my head around to figure out this. I've tried joins and all sorts but i constantly got wrong results.

My sample table looks like below

Item Branch Type
A Main Box
A London Single
A Paris Single
A Tokyo Box
B Main Single
B London Single
B Paris Single
C Main Single
C London Box
C Paris Single
C Tokyo Single
D Main Box
D London Single
D Paris Box
D Tokyo Box
D Sydney Single


what I want to get as my result set is all Items that has a Different 'Type' to its Main 'Branch'

For example if Main Type is Box, display Main branch and other branches that has different Types

result table should look like below.

Item Branch Type
**A Main Box**
A London Single
A Paris Single
**C Main Single**
C London Box
**D Main Box**
D London Single
D Sydney Single


any help is much appreciated.

thanks

vkp vkp
Answer

This is one way to do it to get the rows where the type on any branch differs from the main branch.

select * 
from t t1 
where (branch='Main' or (branch <> 'Main' 
                         and exists (select 1 from t 
                                     where item=t1.item and branch = 'Main' 
                                     and type <> t1.type)
                         )
       )
and item in (select item from t group by item having count(distinct type) > 1)

If the query above seems confusing, you can use a cte to get all the non - main branches whose type differs from the main branch for an item. Thereafter use union all to get the main branch rows for those items.

with y as (
select * from t t1
where branch <> 'Main' and exists (select 1 from t 
                                   where item=t1.item and branch = 'Main' 
                                   and type <> t1.type)
    )
select * from t where branch='Main' and item in (select item from y)
union all
select * from y
Comments