CODEWITHSUNDEEP
oracle-databaseplsql
emvee
emvee

Reputation: 304

Need to know how to SQL query where all a like model numbers differs in description

I need a query where i can query all model numbers and have it return where the same model numbers have different descriptions. I need this information to make all the description a like for the same model of equipment.

Sample Data:

MODEL NUMBER    DESCRIPTION
ASA5520         ASA 5520 adaptive security appliance
ASA5520         Cisco ASA 5520 Appliance
ASA5520         CISCO ASA 5520 APPLIANCE W/ CSC10, SW, 50 USER, 1 YR SUBSCRIPTION
ASA-CSC10       ASA 5500 SERIES CONTENT SECURITY SSM-10 S/W LICENSE
ASA-CSC10       ASA 5500 SERIES CONTENT 
ASA-CSC10       ASA 5500 SSM-10 S/W LICENSE

Upvotes: 0

Views: 52

Answers (2)

Joe
Joe

Reputation: 6827

select *
  from test
where
  model_number in (
    select model_number
      from test
  group by model_number
    having count(distinct description) > 1)

SQLFiddle here

Updated to display all descriptions.

Upvotes: 1

krokodilko
krokodilko

Reputation: 36127

Try semi join:

select *
from tab t1
where exists(
  select 1
  from tab t2
  where t1.model_number = t2.model_number
    and t1.description <> t2.description
)
order by model_number

demo: http://sqlfiddle.com/#!4/827f9/2

Upvotes: 2

Related Questions