SQL Query to fetch a user without an associated service

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.

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.

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'
                 );