SQL Select Data with condition on multiple rows

Question

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:

4          A

or

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

Question is tagged both [tag:mysql] and [tag:sql-server]. Which are you using? — eggyal, Jul 25 '12 at 10:57
For all those, OP wants ID that have exatcly A and B , if it has C too, it should be omitted — Madhivanan, Jul 25 '12 at 11:02

Nikola Markovinović · Answer 1 · 2012-07-25T13:27:36.480

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.

Dan · Answer 2 · 2012-07-25T11:19:01.920

0

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'

EXCEPT

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'

EXCEPT

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.

edited Jul 25 '12 at 11:19

answered Jul 25 '12 at 10:46

Dan

45,079
17
88
157

No it will include ID that may have Name 'C' – Madhivanan Jul 25 '12 at 10:55
Will it? The except clause excludes the set where name = C so there should be none left. I'm fairly confident that this works. – Dan Jul 25 '12 at 11:09
Oh, you used EXCEPT, then it will work I misunderstood as UNION:) – Madhivanan Jul 25 '12 at 11:15

score 0 · Answer 3 · answered Jul 25 '12 at 10:54

0

try:

SELECT distinct ID
FROM tblTemp a
     LEFT JOIN tblTemp b
           ON a.ID = b.ID AND 
              b.name  = 'C'
WHERE b.ID IS NULL;

answered Jul 25 '12 at 10:54

Omesh

27,801
6
42
51

score 0 · Answer 4 · answered Jul 25 '12 at 10:54

0

select id from table
group by id
having min(Name)='A' and max(Nmae)='B'

answered Jul 25 '12 at 10:54

Madhivanan

13,470
1
24
29

score 0 · Answer 5 · answered Jul 25 '12 at 10:55

0

 SELECT ID, Name FROM tblTemp WHERE (Name = 'A' OR Name = 'B') and ID not in(SELECT ID FROM tblTemp WHERE Name = 'C')

answered Jul 25 '12 at 10:55

dnyan waychal

433
1
4
10

SQL Select Data with condition on multiple rows

5 Answers5

Linked