February 6, 2015 at 12:34 am
Hi,
I have the got below T SQL query to fetch the matching record
DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int
)
insert into @MappingTable
VALUES ('Apple', 1,1,1), ('Orange', 1,2,3), ('Guava', 7,8,null);
SELECT Productname
FROM @MappingTable where identification_key1=1 and identification_key2 =2 and identification_key3 =3
-- result - 'Orange'
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
-- result - 'Orange'
Looking forward to your suggestions and help. Thanks in advance
Rajesh
February 6, 2015 at 1:34 am
Quick solution using the APPLY operator
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int
);
insert into @MappingTable(Productname,identification_key1,identification_key2,identification_key3)
VALUES ('Apple' , 1,2,1)
,('Orange', 1,2,3)
,('Guava' , 1,8,9)
,('Kiwi' , 2,8,9);
DECLARE @PROD TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int
);
INSERT INTO @PROD(Productname,identification_key1,identification_key2,identification_key3)
VALUES ('Orange', 1,2,3);
SELECT
MT.Productname
,P.Productname
,(1 - ABS(SIGN(MT.identification_key1 - P.identification_key1)))
+ (1 - ABS(SIGN(MT.identification_key2 - P.identification_key2)))
+ (1 - ABS(SIGN(MT.identification_key3 - P.identification_key3))) AS SCORE
FROM @MappingTable MT
CROSS APPLY @PROD P
ORDER BY SCORE DESC;
Results
Productname Productname SCORE
----------- ----------- -------
Orange Orange 3
Apple Orange 2
Guava Orange 1
Kiwi Orange 0
February 6, 2015 at 1:46 am
Erikur is too quick for me but here's another way. Edit: If you need to limit your results to only those with 2 or more matching criteria, replicate the CASE+CASE+CASE construct in a WHERE clause looking for scores >1.
DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int
)
insert into @MappingTable
VALUES ('Apple', 1,1,1), ('Orange', 1,2,3), ('Guava', 7,8,9);
DECLARE @ID1 INT = 1, @ID2 INT = 2, @ID3 INT = 3;
SELECT TOP 1 WITH TIES Productname
FROM @MappingTable
ORDER BY CASE identification_key1 WHEN @ID1 THEN 1 ELSE 0 END +
CASE identification_key2 WHEN @ID2 THEN 1 ELSE 0 END +
CASE identification_key3 WHEN @ID3 THEN 1 ELSE 0 END
DESC;
SELECT @ID1 = 1, @ID2 = 2, @ID3 = 1;
SELECT TOP 1 WITH TIES Productname
FROM @MappingTable
ORDER BY CASE identification_key1 WHEN @ID1 THEN 1 ELSE 0 END +
CASE identification_key2 WHEN @ID2 THEN 1 ELSE 0 END +
CASE identification_key3 WHEN @ID3 THEN 1 ELSE 0 END
DESC;
Note that your second query will also return Apple.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 6, 2015 at 2:03 am
dwain.c (2/6/2015)
Erikur is too quick for me but here's another way:
😀
The logic is exactly the same, I just short-handed the CASE statement with a little math. Note that the math approach is marginally slower although in most applications the difference is hardly noticeable.
😎
February 6, 2015 at 2:28 am
Hi Erikur and Dwain,
Thanks very much for looking into it.
Queries are amazing and works very well as expected. Perfect!!
Dwain's answer looks closer to what I expected as some of my columns in the original table have null values in it and it handles those very well.
Actually, I should have been more clearer by adding null data values in the original example
Cheers,
Rajesh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply