Filtering a table based on multiple criteria where an items attributes are entered as separate rows-CodePudding

I have inherited a SQL Server table design as detailed below. The table is called Info.

I need to understand the SQL statement required to return the correct results for the following two examples.

Id  | ItemId | FieldName | Value
 1  | 302    | Colour    | Red
 2  | 303    | Length    | 100
 3  | 304    | Length    | 25
 4  | 305    | Colour    | Blue
 5  | 306    | Colour    | Blue
 6  | 306    | Length    | 100
 7  | 307    | Colour    | Blue
 8  | 307    | Length    | 35
 9  | 308    | Colour    | Red
 10 | 308    | Length    | 100
 11 | 309    | Colour    | Red
 12 | 309    | Length    | 45
 13 | 309    | Shape     | Square
 14 | 310    | Shape     | Round

Example 1: I need to find out which Items (ItemId) have a Colour of "Red" AND a Length of "100" from the "Info" table - which should be only, ItemId 308

My limited SQL knowledge has taken me down the path of

SELECT ItemId 
FROM Info 
WHERE (FieldName = 'Colour') 
  AND (Value = 'Red')

but this returns Items 302 and 308, the similar query of

SELECT ItemId 
FROM Info 
WHERE (FieldName = 'Length') 
  AND (Value = '100')

returns 303 and 308, when the correct output should just be ItemId 308

The scenario I have may also require the expansion of this query to include more than two fields being defined as in the following example:

Example 2: I need to find out which Items have a Colour of "Red" AND a Length of "45" AND are "Square" - which should be only ItemId 309

Any advice would be gratefully received.

CodePudding user response：

As stated in the comments, you can use an INTERSECT to accomplish what you are looking for. Examples for both of your questions would be:

Items with Colour = Red and Length = 100

select ItemId
from Info
where FieldName = 'Colour' and Value = 'Red'

intersect

select ItemId
from Info
where FieldName = 'Length' and Value = '100'

Items with Colour = Red, Length = 45, and Shape = Square

select ItemId
from Info
where FieldName = 'Colour' and Value = 'Red'

intersect

select ItemId
from Info
where FieldName = 'Length' and Value = '45'

intersect

select ItemId
from Info
where FieldName = 'Shape' and Value = 'Square'

CodePudding user response：

I think this will get what you're asking. I use LEFT OUTER JOINs to join for each parameter that you're searching against. Then at the top I use a SELECT DISTINCT to get the ItemId.

DECLARE @SearchColor nvarchar(50) = 'Red';
DECLARE @SearchLength nvarchar(50) = '100';

SELECT DISTINCT i.ItemId
FROM Info as i
    LEFT OUTER JOIN Info as iColor
        ON iColor.ItemId = i.ItemId 
        AND iColor.FieldName = 'Colour'
        AND iColor.Value = @SearchColor
    LEFT OUTER JOIN Info as iLength
        ON iColor.ItemId = i.ItemId 
        AND iColor.FieldName = 'Length'
        AND iColor.Value = @SearchLength
WHERE iColor.Id IS NOT NULL
    AND iLength.Id IS NOT NULL
;

CodePudding user response：

Here's a way to get both results in one query and keep all the info.

select   Id
        ,ItemId 
        ,FieldName  
        ,Value
from     (
          select   *
                  ,count(case when value = 'Red' then 1 when try_convert(int, value) = 100  then 1 end) over(partition by ItemId)                            as mrk 
                  ,count(case when value = 'Red' then 1 when try_convert(int, value) = 45 then 1 when value = 'Square' then 1 end) over(partition by ItemId) as mrk2 
          from     info
         ) info
where    mrk  = 2 
   or    mrk2 = 3

Id	ItemId	FieldName	Value
9	308	Colour	Red
10	308	Length	100
11	309	Colour	Red
12	309	Length	45
13	309	Shape	Square

Fiddle

CodePudding user response：

First example:

select    ItemId
from     (
          select   ItemId
                  ,case when value = 'Red' then 1 end                   as mrk
                  ,case when try_convert(int, value) = 100  then 1 end  as mrk2
          from     info
         ) info
group by  ItemId
having    max(mrk)  = 1
   and    max(mrk2) = 1

ItemId
308

Second example:

select    ItemId
from     (
          select   ItemId
                  ,case when value = 'Red' then 1 end                  as mrk
                  ,case when try_convert(int, value) = 45  then 1 end  as mrk2
                  ,case when value = 'Square' then 1 end               as mrk3
          from     info
         ) info
group by  ItemId
having    max(mrk)  = 1
   and    max(mrk2) = 1
   and    max(mrk3) = 1

ItemId
309

Fiddle

CodePudding user response：

We can pivot and simply retrieve the info.

select *
from
(
select ItemId, FieldName, Value
from   info
) info
pivot(max(Value) for FieldName in(Colour, Length, Shape)) p
where Colour = 'red' and Length = '100'
   or Colour = 'red' and Length = '45' and shape = 'square'

ItemId	Colour	Length	Shape
308	Red	100	null
309	Red	45	Square

Fiddle

CodePudding user response：

Try this:

SELECT ItemId 
FROM Info 
WHERE (FieldName = 'Colour' AND Value = 'Red')
AND (FieldName = 'Length' AND Value = 100)