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

  • Text message via SQL Server
  • T-SQL trim &nbsp (and other non-alphanumeric characters)
  • How to use Zend Framework 2 with MS SQL Server on *nix?
  • How to replace parentheses in a column
  • How can I list all foreign keys referencing a given table in SQL Server?
  • SQL selection join help
  • 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  
    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”

    Try below

    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
    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
    by     basicid
    having count(*) = 2 /*number of filters*/

    Try this:

    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

    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.
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.