CODEWITHSUNDEEP
sqlsql-server
Kian
Kian

Reputation: 65

Select distinct fields with criteria

I'm an amateur in SQL scripts and I need your help. I have the following query which gives me 4 rows. 

SELECT A.Id,
       A.InterfaceId,
       I.DeviceId,
       I.TypeId,
       A.AnoType
FROM I 
INNER JOIN A ON I.InterfaceId = A.InterfaceId
INNER JOIN D ON I.DeviceId = D.DeviceId
WHERE I.AccountId = '500'
  AND D.UserId = '1000'

The result of this query is:

  Id                  InterfaceId                            DeviceId                 TypeId AnoType
----------------------------------------------------------------------------------------------------
1216046 | C4BEB5D8-8749-4831-92F4-070CF58F7C81 | B0E8C423-1B01-4AD5-A383-B0E584E6176C | 1207 | -40 |
1216047 | F00F87BD-F1B4-4DAD-801A-E022403831D5 | B0E8C423-1B01-4AD5-A383-B0E584E6176C | 1208 | -40 |
1216329 | F00F87BD-F1B4-4DAD-801A-E022403831D5 | B0E8C423-1B01-4AD5-A383-B0E584E6176C | 1208 | -3  |
1225762 | F00F87BD-F1B4-4DAD-801A-E022403831D5 | B0E8C423-1B01-4AD5-A383-B0E584E6176C | 1208 | -20 |
----------------------------------------------------------------------------------------------------

My problem is that I need a query which returns the rows that their AnoType is not -3 and its InterfaceId doesn't have a -3 value for AnoType in other rows. For example in third row the AnoType is -3, so my query should return only the first row and no other rows. Any help would be greatly appreciated.

Thanks in advance,

Kian

Upvotes: 2

Views: 71

Answers (1)

M.Ali
M.Ali

Reputation: 69524

SELECT A.Id,
       A.InterfaceId,
       I.DeviceId,
       I.TypeId,
       A.AnoType
FROM I 
INNER JOIN A ON I.InterfaceId = A.InterfaceId
INNER JOIN D ON I.DeviceId = D.DeviceId
WHERE I.AccountId = '500'
  AND D.UserId = '1000'
  AND A.AnoType <> -3
  AND A.InterfaceId  NOT IN (SELECT InterfaceId
                             FROM A
                             WHERE AnoType = -3)

Upvotes: 1

Related Questions