SQL Select Data with condition on multiple rows

Suppose that I have a table called “tblTemp” with the following data

ID        Name  
1           A  
2           B  
3           C  
4           A  
4           B  
5           A  
5           B  
5           C  
6           C  
6           B

I want to get ID from name of A&B only not A&B&C like below:

  • SQL Server Datetime object persistent reformatting issue in Excel
  • Creating a case insensitive SQLAlchemy query for MS-SQL
  • Using Ubuntu, how do I install DBD::Sybase from CPAN?
  • in sql server what is the difference between user_type_id and system_type_id in sys.types
  • select the TOP N rows from a table
  • How can I escape square brackets in a LIKE clause?
  • 4          A 


    4          B

    How can I do like this in sql?

    I try the following sql but it return row 5 as well:

    SELECT     tblTemp.ID, tblTemp.Name
    FROM         tblTemp INNER JOIN
                      tblTemp AS tbltemp_1 ON tblTemp.ID = tbltemp_1.ID
    WHERE     (tblTemp.Name = 'A') AND (tbltemp_1.Name = 'B')

  • How to resolve SqlConnection error “Keyword not supported: 'decr pool size'.”
  • SQL Server : How to test if a string has only digit characters
  • Ignore certain columns when using BULK INSERT
  • SQL Server NOLOCK and joins
  • Visual Studio 2012 does not create new SQL table
  • SQL query - Select * from view or Select col1, col2, … colN from view
  • 5 Solutions collect form web for “SQL Select Data with condition on multiple rows”

    One of the ways to compare sets is to take the count of group, filter groups by search set, and see if number of matches per group equals original number of group members:

    select tblTemp.ID
      from tblTemp
     inner join
       select ID,
              count(*) GroupCount
         from tblTemp
        group by ID
       having count(*) = 2
     ) g
       on tblTemp.ID = g.ID
     where tblTemp.Name in ('A', 'B')
     group by tblTemp.Id, g.GroupCount
    having count (*) = g.GroupCount

    This should work on both MySql and Sql Server.

    You can play with this code @ Sql Fiddle.


    SELECT distinct ID
    FROM tblTemp a
         LEFT JOIN tblTemp b
               ON a.ID = b.ID AND 
                  b.name  = 'C'
    select id from table
    group by id
    having min(Name)='A' and max(Nmae)='B'
     SELECT ID, Name FROM tblTemp WHERE (Name = 'A' OR Name = 'B') and ID not in(SELECT ID FROM tblTemp WHERE Name = 'C') 

    Do you just want a list of distinct IDs that have A or B but not C?

    SELECT distinct ID
    FROM tblTemp
    WHERE Name = 'A' or Name = 'B'
    SELECT distinct ID
    FROM tblTemp
    WHERE Name = 'C'

    My above solution work for ‘A’ and/or ‘B’, but I notice you actually want ‘A’ and ‘B’ with no or. In that case:

    SELECT distinct ID
    FROM tblTemp as T1
    INNER JOIN tblTemp as T2
    ON T1.ID = T2.ID
    WHERE T1.Name = 'A' and T2.Name = 'B'
    SELECT distinct ID
    FROM tblTemp
    WHERE Name = 'C'

    this is an extension of your original code and is perhaps not as elegant as @Madhivanan’s solution but it is more general should A B and C change to words for example.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.