Select column value that matches a combination of other columns values on the same table
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
4 Solutions collect form web for “Select column value that matches a combination of other columns values on the same table”
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
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
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.
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)
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.