Select column value that matches a combination of other columns values on the same table

Question

I have a table called Ads and another Table called AdDetails to store the details of each Ad in a Property / Value style, Here is a simplified example with dummy code:

[AdDetailID], [AdID], [PropertyName], [PropertyValue]
 2            28      Color           Red
 3            28      Speed           100
 4            27      Color           Red
 5            28      Fuel            Petrol  
 6            27      Speed           70

How to select Ads that matches many combinations of PropertyName and PropertyValue, for example :

where PropertyName='Color' and PropertyValue='Red'
And
where PropertyName='Speed' and CAST(PropertyValue AS INT) > 60

What you have there is an EAV database design. What you are finding is that they are notoriously difficult to query http://weblogs.sqlteam.com/davidm/articles/12117.aspx — podiluska, Jun 09 '14 at 15:37

JBrooks · Accepted Answer · 2014-06-09T21:53:13.257

You are probably going to do stuff like this a lot so I would start out by making a view that collapses all of the properties to a single row.

create view vDetail
as
select AdID,
max(case PropertyName 
when 'Color' then PropertyValue end) as Color,
cast(max(case PropertyName 
when 'Speed' then PropertyValue end) as Int) as Speed,
max(case PropertyName 
when 'Fuel' then PropertyValue end) as Fuel
from AdDetails
group by AdID

This approach also solves the problem with casting Speed to an int.

Then if I select * from vDetails

enter image description here

This makes it easy to deal with when joined to the parent table. You said you needed a variable number of "matches" - note the where clause below. @MatchesNeeded would be the count of the number of variables that were not null.

        select *
        from Ads a
        inner join vDetails v
        on a.AdID = v.AdID
        where case when v.Color = @Color then 1 else 0 end +
          case when v.Spead > @Speed then 1 else 0 end +
          case when v.Fuel = @Fuel then 1 else 0 end = @MatchesNeeded

This solution takes a small amount of work up front to make large gains in simplification down the line. It should make your subsequent queries much easier to deal with. Assuming your property types are somewhat static, this is a great solution. — Steve Bryant, Jun 09 '14 at 21:47

score 1 · Answer 2 · edited May 23 '17 at 10:26

I think you have two main problems to solve here.

1) You need to be able to CAST varchar values to integers where some values won't be integers.

If you were using SQL 2012, you could use TRY_CAST() ( sql server - check to see if cast is possible ). Since you are using SQL 2008, you will need a combination of CASE and ISNUMERIC().

2) You need an efficient way to check for the existence of multiple properties.

I often see a combination of joins and where clauses for this, but I think this can quickly get messy as the number of properties that you check gets over... say one. Instead, using an EXISTS clause tends to be neater and I think it provides better clues to the SQL Optimizer instead.

 SELECT AdID
 FROM   Ads
 WHERE  1 = 1
    AND EXISTS (
            SELECT  1
            FROM    AdDetails
            WHERE   AdID = Ads.AdID
                AND ( PropertyName='Color' and PropertyValue='Red' )
        )
    AND EXISTS (
            SELECT  1
            FROM    AdDetails
            WHERE   AdID = Ads.AdID
                AND PropertyName='Speed'
                AND 
                    (
                        CASE
                        WHEN ISNUMERIC(PropertyValue) = 1
                        THEN CAST(PropertyValue AS INT)
                        ELSE 0
                        END
                    )
                    > 60
        )

You can add as many EXISTS clauses as you need without the query getting particularly difficult to read.

score 0 · Answer 3 · answered Jun 09 '14 at 15:39

0

Something like this might work for 2 conditions, you would have to adapt depending on the number of conditions

select a.*
from ads as a
    join addetails as d1 on d1.adid = a.id
    join addetails as d2 on d2.adid = a.id

where (d1.PropertyName='Color' and d1.PropertyValue='Red')
        and (d2.PropertyName='Speed' and d2.CAST(PropertyValue AS INT) > 60)

answered Jun 09 '14 at 15:39

@Gray, Thanks for your answer, i am using that in search page to find Ads for certain details, What if the user entered 10 conditions, I think the performance will be really bad, Do you think that using cursors will perform better? Any other suggestions please? – Alaa Alweish Jun 09 '14 at 15:48
How many records are we looking at? How many in Ads? How many in AdDetails? – Jun 09 '14 at 15:54
Say 2000+ Ads , each Ad has 15+ Properties, The Ad Details table might contains 50,000+ properties, and the search request might contains 10 conditions in one shot. – Alaa Alweish Jun 09 '14 at 16:03
What kind of structure are you using to pass a variable number of conditions to the db? – Jun 09 '14 at 16:08
I am building the query and the where clause in run-time, BTW, I just learned from podiluska's comment that what i am doing is called EAV Database design. – Alaa Alweish Jun 09 '14 at 16:13
If you are building this at run time (dynamically) then I would suggest inner joining to the details table for each condition, it might not look pretty but the sql optimizer should be able to pick out the best approach. A cursor would not help in this case. – Jun 09 '14 at 16:17

score 0 · Answer 4 · answered Jun 09 '14 at 17:12

DECLARE @AdDetails TABLE
( 
    AdDetailID INT, 
    AdID INT, 
    PropertyName VARCHAR(20), 
    PropertyValue VARCHAR(20)
)

INSERT INTO @AdDetails
( AdDetailID, AdID, PropertyName, PropertyValue )
VALUES
(2,           28,      'Color',           'Red'),
(3,           28,      'Speed',           '100'),
(4,           27,      'Color',           'Red'),
(5,           28,      'Fuel',           'Petrol'),
(6,           27,      'Speed',           '70');


--Col1
DECLARE @ColorValue VARCHAR(20) = 'Red'

--Col2
DECLARE @SpeedValue INT  = 90
DECLARE @SpeedType VARCHAR(2) = '>'

--Col3
DECLARE @FuelValue VARCHAR(20) = null

SELECT DISTINCT a.AdID FROM @AdDetails a
INNER JOIN
(
    SELECT * 
    FROM @AdDetails 
    WHERE @ColorValue IS NULL 
        OR @ColorValue = PropertyValue
) Color
    ON Color.AdID = a.AdID
INNER JOIN
(
    SELECT * 
    FROM @AdDetails 
    WHERE @SpeedType IS NULL
    UNION
    SELECT * 
    FROM @AdDetails
    WHERE PropertyName = 'Speed'
        AND ((@SpeedType = '>' AND CONVERT(INT, PropertyValue) > @SpeedValue)
        OR (@SpeedType = '<' AND CONVERT(INT, PropertyValue) < @SpeedValue)
        OR (@SpeedType = '=' AND CONVERT(INT, PropertyValue) = @SpeedValue))
) AS Speed
    ON Speed.AdID = a.AdID
INNER JOIN
(
    SELECT * 
    FROM @AdDetails 
    WHERE @FuelValue IS NULL 
        OR (@FuelValue = PropertyValue)
) AS Fuel
    ON Fuel.AdID = a.AdID

I add one inner join clause per property type (with some overrides), your sql query would pass all of the possible property type info in one go nulling out whatever they don't want. very ugly code though as it grows.

Select column value that matches a combination of other columns values on the same table

4 Answers4

Linked