Sql query for AND on multiple rows
I have a table (tblABC) which looks like
----------------------------- BasicID | Filter 1| Filter2 | ------------------------------ 100 1 2 100 3 4 101 8 9
I want to select the BasicID which has Filter1=1 and Filter2=4. ie I want to get the output as
I can’t be using AND here as as it searches within the same row only. ie
select * from tblABC where Filter1=1 and Filter2=4 , yields no result.
As of now the query that I use is
select * from tblABC where and BasicID in ( select BasicID from tblABC where Filter1 IN (1) ) and BasicID in ( select BasicID from tblABC where Filter2 IN (4) )
This one works for me. But there are like 12 Filter columns and when such a string search is run in large volumes, wouldn’t it make the query slow. What would be a more efficient way of doing this?
I am using Microsoft SQL 2014
6 Solutions collect form web for “Sql query for AND on multiple rows”
select basicid from tblABC where filter1 = 1 intersect select basicid from tblABC where filter1 = 4
If the two filter values can be either in different rows or the same row, the
GROUP BY / HAVING method will fail (in the same row case). This method will work in all cases (the
intersect query by @Azar will work, too):
select distinct a.BasicID from tblABC as a join tblABC as b on a.BasicID = b.BasicID where a.Filter1 = 1 and b.Filter2 = 4 ;
If you want the
GROUP BY / HAVING COUNT method, this modification will work in all cases, too:
select basicid from tblABC where filter1 = 1 or filter2 = 4 group by basicid having count(case when filter1 = 1 then 1 end) >= 1 and count(case when filter2 = 4 then 1 end) >= 1 ;
You can use
group by and a
count if you are sure one line doesn’t match multiple rules (then the count might be off and the query breaks).
select basicid from tblABC where filter1 = 1 or filter2 = 4 group by basicid having count(*) = 2 /*number of filters*/
SELECT BasicID FROM tblABC WHERE Filter1=1 OR Filter2=4 GROUP BY BasicID HAVING COUNT(BasicID)=2
In case of 12 filters, change COUNT to be 12.
You can use the following query
SELECT DISTINCT BasicId FROM tblABC main WHERE Filter1 = 1 AND EXISTS (SELECT TOP 1 Filter2 FROM tblABC WHERE tblABC.BasicId = main.BasicId AND Filter2 = 4)
You have used the IN clause. You can easily create three additional variants: using exists instead of in, using intersection, and using a selfjoin of tblABC.
But as far as I know, query optimizers can translate automatically your query into these three variants and even it could get more non-trivial rewritings.
I suggest you:
- First, let the optimizer work.
- Second, if your query is finally slow then create (if not exists) a index over BasicID.
- Third, if the query is still slow then use two bitmap indexes, over filter1 and over filter2.
- And fourth if it is still slow use and indexed view.