CODEWITHSUNDEEP
sqlsql-serverselect
user2561997
user2561997

Reputation: 443

Select Condition Based on Multiple rows

I have a Table Like this:

TableA
----------------------------
ID   - Name  - PatID
1       A         10
2       B         10
3       A         11
4       A         12
5       B         13

I want to select All Such PatID Which has Name=A and Name = B.

So i should only get 10 as result.

What should be query for this?

Upvotes: 1

Views: 128

Answers (2)

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

If you need information from the A group and the B group you could also do it like this:

SELECT AGroup.ID AS AId, BGroup.ID AS BId
FROM TableA AGroup
JOIN TableB BGroup
    ON AGroup.Name = 'A'
    AND BGroup.Name = 'B'
    AND AGroup.PatID = BGroup.PatID

This also retains duplicates if you have more than one entry, for Name = A and PatID = 10 for example.

Upvotes: 0

Taryn
Taryn

Reputation: 247620

You should be able to use the following query to get the result:

select patid
from tablea
where name in ('A', 'B')
group by patid
having count(distinct name) = 2;

See SQL Fiddle with Demo

Upvotes: 2

Related Questions