asked
Multiple fields Search
Dear Friends, I've a criticla requirements for searching multiple fields based on a value in the same row.. I'll explain it with my table structure. tbl_reportData --- ID TestTakenID CapacityName Score --------------------------------------------------------- 1 5 ABCD 5 2 5 EFGH 6.2 3 5 HIJK 4.5 4 5 LMNO 3.2 5 6 ABCD 2 6 6 EFGH 7 7 6 HIJK 3.5 8 6 LMNO 6 9 7 ABCD 4.3 10 7 EFGH 9.1 11 7 HIJK 6.5 12 7 LMNO 5 13 8 ABCD 4.3 14 8 EFGH 8.5 15 8 HIJK 6.5 16 8 LMNO 5.3 -------------------------------------------------- In this way there is around 20k + rows in that table My question is straight forward I've to find all TestTakenID where value for LMNO between 5 and 6 and value for EFGH between 8 and 9.5. So the result will be TestTakenID : **7,8** I done it using intersect but it seems to be a bad solution since it traverse the whole rows on every statements. So requesting you all to share some special query for this. Thanks in advance
I think you were looking for something like this ;WITH CTE AS ( SELECT CASE WHEN [CapacityName] = 'LMNO' AND [Score] BETWEEN 5 AND 6 THEN 1 ELSE NULL END AS IsValidLMNO, CASE WHEN [CapacityName] = 'EFGH' AND [Score] BETWEEN 8 AND 9.5 THEN 1 ELSE NULL END AS IsValidEFGH , [TestTakenId] FROM #table AS T ) SELECT [TestTakenId] , COUNT([IsValidLMNO]), COUNT([IsValidEFGH]) -- YOU CAN ALWAYS COMMENT THIS LINE. IT IS JUST FOR CLARITY FROM [CTE] GROUP BY [TestTakenId] HAVING COUNT([IsValidLMNO]) >= 1 AND COUNT([IsValidEFGH]) >= 1 I have made the comparison with INTERSECT Query, Mr. ThomasRushton's second last query (modified a bit to my environment and added DISTINCT in the SELECT clause as per requirement) i.e. -- INTERSECT SOLUTION SELECT DISTINCT TestTakenID FROM #table WHERE CapacityName = 'LMNO' AND Score BETWEEN 5 AND 6 INTERSECT SELECT DISTINCT TestTakenID FROM #table WHERE CapacityName = 'EFGH' AND Score BETWEEN 8 AND 9.5 -- Mr. ThomasRushton's Solution SELECT DISTINCT TestTakenID FROM #table WHERE CapacityName = 'LMNO' AND Score BETWEEN 5 AND 6 AND TestTakenID IN ( SELECT TestTakenID FROM #table WHERE CapacityName = 'EFGH' AND Score BETWEEN 8 AND 9.5 ) For 1,600,000 rows without any index for the IO Statistics, my solution was better than both. My solution was first, Mr. ThomasRushton's solution second and INTERSECT was third. But this not ends here. If I add an index like CREATE NONCLUSTERED INDEX IX_TEMP ON [#table] ([CapacityName], [Score]) INCLUDE ([TestTakenId]) INTERSECT and Mr. ThomasRushton's solution seems to be outperforming my solution as expected. For IO STATISTICS Mr. ThomasRushton's solution was first, INTERSECT was second and mine was third. There is much more to explore for these solutions but I leave it to you. The lesson to be learn here is, every solution's performance will always depend upon the environment as different solutions with different data structures may outperform each other. You must test all with different implementations according to the environment. Cheers.
SELECT DISTINCT TestTakenID FROM t WHERE (CatacityName='LMNO' AND Score BETWEEN 5 AND 6) OR (CapacityName='EFGH' AND Score BETWEEN 8 AND 9.5)
Hi magnus.. Thankyou for your reply. Unfortunately this is not my desired result. In your given way it will not go for both the condition. it will return rows with any of the conditions we mentioned. Hope you got my explanation. That means our result should not return any TestTakenId having CapacityName='LMNO' and Score Between 5 and 6. Our result must validate both the condition against each TestTakenID.
**Hi magnus.. Thankyou for your reply. Unfortunately this is not my desired result. In Magnus given way it will not work for both the condition. it will return rows with any of the conditions we mentioned. Hope you got my explanation. That means our result should not return any TestTakenId having CapacityName='LMNO' and Score Between 5 and 6. Our result must validate both the condition against each TestTakenID.** Actually thats the reason why I think about intersection and then I found its not an optimal solution. I'm sure there will be some perfect solutions you guys can give.. Thanks in advance
Something like this: ;with Score1 AS (SELECT TestTakenID FROM @tbl_ReportData WHERE CapacityName = 'LMNO' AND Score Between 5 AND 6), Score2 AS (SELECT TestTakenID FROM @tbl_ReportData WHERE CapacityName = 'EFGH' AND Score Between 8 AND 9.5) SELECT Score1.TestTakenID FROM Score1 INNER JOIN Score2 ON Score1.TestTakenID = Score2.TestTakenID OK, so you don't like the CTE. How's about using a subquery instead? Here's a couple of methods: select TestTakenID FROM @tbl_ReportData WHERE CapacityName = 'LMNO' AND Score between 5 AND 6 AND TestTakenID IN (SELECT TestTakenID FROM @tbl_ReportData WHERE CapacityName = 'EFGH' AND Score Between 8 AND 9.5) SELECT TestTakenID FROM @tbl_ReportData t1 WHERE CapacityName = 'LMNO' AND Score Between 5 AND 6 AND t1.TestTakenID = (SELECT t2.TestTakenID FROM @tbl_ReportData t2 WHERE t2.CapacityName = 'EFGH' AND t2.Score BETWEEN 8 AND 9.5 AND t1.TestTakenID = t2.TestTakenID)
Dear thomas, Thank you for that with CTE. But I'm afraid it will work the same as
intersect works. I'm not saying its a wrong one in anyway.. But am looking for something which returns the result in a single query rather than mixing up queries.. Sorry if no way for that...Then I will proceed with intersect or CTE
Thanks a lot friends, At end I finalised with Intersect with Indexing as Usman Butt and Thomas mentioned. But I didn't mention an important point which these conditions are dynamic. That means user in my
ASP.NET page will decide how many conditions will be there. So the number of conditions will be more than two since there is a lot of CapacityNames there in the Table. So I'm passing those conditions as arguments to the stored procedure and mixed up with the select query. So with CTE it is difficult and thats why I finalized to proceed with intersection and indexing.. Thanks a lot for the support...
