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.
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)
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...