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:
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')
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' WHERE b.ID IS NULL;
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' 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.