I have the below data in a table. I am trying to fetch all the “Modem” users, who do not have an associated telephone service.
x
UserID DeviceNumber DeviceType DeviceRole
1 A Telephone SingleUser
1 A Modem MultiUser
1 B Modem MultiUser
2 C Telephone SingleUser
2 C Modem MultiUser
2 D Modem MultiUser
select distinct t.* from table t
join table t1 on t1.UserID= v.UserID
and t1.DeviceNumber <> t.DeviceNumber
and t.DeviceType = 'Modem';
I want to see DeviceNumber B and D in my output. But above query is not returning expected results.
Advertisement
Answer
Hmmm . . . One method would be:
select t.*
from t
where t.devicetype = 'Modem' and
not exists (select 1
from t t2
where t2.userid = t.userid and t2.devicenumber = t.devicenumber and
t2.devicetype = 'Telephone'
);