Join and select only if left side rows contain all the declared rows-CodePudding

Firstly, my apologies for the vague tittle. I have two tables: Devices and DeviceProperties.

[ Devices ]    
[ id ]
[ AA ]
[ BB ]
[ CC ]

[ DeviceProperties ]
[ id ][ propertyType ][  propertyKey ][ valueType ][      value ]
[ AA ][      desired ][ scanInterval ][    Number ][        100 ]
[ AA ][          tag ][        floor ][    Number ][        200 ]
[ AA ][      desired ][         name ][    String ][  AA_Device ]
[ BB ][      desired ][ scanInterval ][    Number ][        100 ]
[ BB ][          tag ][        floor ][    Number ][        200 ]
[ CC ][          tag ][        floor ][    Number ][        200 ]

I want to select all devices that have specific device properties:

-- Select devices that have these DeviceProperties
[      desired ][ scanInterval ][    Number ][        100 ]
[          tag ][        floor ][    Number ][        200 ]

I declare N variables. Each group of 4 declared variables represent a different device property/row in DeviceProperties table.

DECLARE @propertyType_1 nvarchar(max) = 'desired';
DECLARE @propertyKey_1 nvarchar(max) = 'scanInterval'
DECLARE @valueType_1 nvarchar(max) = 'Number'
DECLARE @value_1 nvarchar(max) = '100'
-- [      desired ][ scanInterval ][    Number ][        100 ]

DECLARE @propertyType_n nvarchar(max) = 'tag';
DECLARE @propertyKey_n nvarchar(max) = 'floor'
DECLARE @valueType_n nvarchar(max) = 'Number'
DECLARE @value_n nvarchar(max) = '200'
-- [          tag ][        floor ][    Number ][        200 ]

Assuming I had a working query, it would only return the following devices:

[ Devices ]    
[ id ]
[ AA ]
[ BB ]

Device CC would not be returned because it only has DeviceProperty

[          tag ][        floor ][    Number ][        200 ]

but not

[      desired ][ scanInterval ][    Number ][        100 ]

I'm really confused as to what sort of a query I should be writing. A simple WHERE query does not work here.

CodePudding user response：

Insert the required matching criteria in a temp table

insert into #temp (propertyType, propertyKey, valueType, value)
values ('desired', 'scanInterval', 'Number', 100),
       ('tag',     'floor',        'Number', 200);

Join it to your table, GROUP BY id and count for matching rows must be N. For the example given, N = 2

SELECT d.id
FROM   Devices d
       INNER JOIN DeviceProperties p ON  d.id = p.id
       INNER JOIN #temp t            ON  p.propertyType = t.propertyType
                                     AND p.propertyKey  = t.propertyKey
                                     AND p.valueType    = t.valueType
                                     AND p.value        = t.value
GROUP BY d.id
HAVING COUNT(*) = 2

CodePudding user response：

Squirrel gave me an idea. Not sure if this is an ideal approach or not but I could also do something like this:

select
    d.deviceId
from
    Devices as d
join
    DeviceProperties as p
on
    d.deviceId = p.deviceId
where
    p.propertyType in (@pt_0, @pt_1) and 
    p.propertyKey in (@pk_0, @pk_1) and 
    p.value in (@pv_0, @pv_1)
group by
    d.deviceId
having
    COUNT(*) = 2