How to return rows based on one, specific value (and not with any other value)?

Question

Let's say I have 2 tables:

Person
+----------+---------+
| PersonID | Email   |
+----------+---------+
|        1 | a@a.com |
|        2 | b@b.com |
+----------+---------+

And

PersonType
+--------------+----------+-------+
| PersonTypeID | PersonID | pType |
+--------------+----------+-------+
| 1            |        1 |     1 |
| 2            |        2 |     1 |
| 3            |        2 |     2 |
+--------------+----------+-------+

I would like to get rows of Person where pType equals ONLY 1 and not any other value in the PersonType table. So something like this

SELECT * 
FROM Person P 
INNER JOIN PersonType PT ON P.PersonID = PT.PersonID AND pType=1;

But that returns

+----------+---------+--------------+----------+-------+
| PersonID | Email   | PersonTypeID | PersonID | pType |
+----------+---------+--------------+----------+-------+
|        1 | a@a.com | 1            |        1 |     1 |
|        2 | b@b.com | 2            |        2 |     1 |
+----------+---------+--------------+----------+-------+

I would like it to return only PersonID=1 because PersonID=2 has pType of 1 and 2.

+----------+---------+--------------+----------+-------+
| PersonID | Email   | PersonTypeID | PersonID | pType |
+----------+---------+--------------+----------+-------+
|        1 | a@a.com | 1            |        1 |     1 |    
+----------+---------+--------------+----------+-------+

Is that possible? I tried all variations of joins using not IN, etc. and can't seem to wrap my head around this.

Also, in addition to this, I would like to know how to return rows with pType =1 AND 2. Something like:

+----------+---------+--------------+----------+-------+
| PersonID | Email   | PersonTypeID | PersonID | pType |
+----------+---------+--------------+----------+-------+
|        2 | b@b.com | 2            |        2 |     1 |
|        2 | b@b.com | 3            |        2 |     2 |
+----------+---------+--------------+----------+-------+

PersonID 2 has both PType 1 & 2, so I would only like to return that Person. How would I do this as well? Thank you.

EDIT

Using Bohemian's answer, I came up with something to solve the case of finding rows for pType =1 AND 2:

SELECT P.PersonID, P.Email, PT.pType  
FROM Person P 
INNER  JOIN PersonType PT ON P.PersonID = PT.PersonID 
LEFT JOIN PersonType PT2 ON P.PersonID = PT2.PersonID AND PT2.ptype != 2 
LEFT JOIN PersonType PT3 ON P.PersonID = PT3.PersonID AND PT3.pType != 1 
WHERE PT2.ptype IS NOT NULL AND PT3.pType IS NOT NULL;

That seems to work. Does anyone have anything better for this case?

Answer 1

Instead of NOT EXISTS, you can try a LEFT JOIN:

SELECT *
FROM Person p
JOIN PersonType pt ON p.PersonID = pt.PersonID
LEFT JOIN (
  SELECT PersonID
  FROM PersonType
  WHERE
      PType > 1
) v ON v.PersonID = p.PersonID
WHERE
  pt.PType = 1
  AND v.PersonID IS NULL

Answer 2

Add another outer join to other types and filter for non hits on it:

SELECT * 
FROM Person P 
JOIN PersonType PT ON P.PersonID = PT.PersonID AND PT.pType=1
LEFT JOIN PersonType PT2 ON P.PersonID = PT2.PersonID AND PT2.pType != 1
WHERE PT2.pType IS NULL —- this is only null when there are no hits for other types

You can also use the more mundane NOT EXISTS:

SELECT * 
FROM Person P 
JOIN PersonType PT ON P.PersonID = PT.PersonID AND PT.pType=1
WHERE NOT EXISTS (
    SELECT * FROM PersonType PT2
    WHERE P.PersonID = PT2.PersonID AND PT2.pType!=1)

but it’s less efficient as it uses a correlated subquery (that although may be optimized away, it still poor practice).

Answer 3

If you only want the persons and not the rows from person type, then you can use exists and not exists:

select p.*
from person p
where exists (select 1
              from persontype pt
              where pt.personid = p.personid and 
                    pt.ptype = 1 
             ) and
      not exists (select 1
                  from persontype pt
                  where pt.personid = p.personid and
                        pt.ptype <> 1
                 );

The more general solution, however, would use group by and having. For just type 1:

select pt.personid
from persontype pt
group by pt.personid
having sum(case when pt.type = 1 then 1 else 0 end) > 0 and
       sum(case when pt.type <> 1 then 1 else 0 end) = 0;

For types 1 and 2 and no others:

select pt.personid
from persontype pt
group by pt.personid
having sum(case when pt.type = 1 then 1 else 0 end) > 0 and
       sum(case when pt.type = 2 then 1 else 0 end) > 0 and
       sum(case when pt.type not in (1, 2) then 1 else 0 end) = 0;

Note: The first of these is often simplified to:

select pt.personid
from persontype pt
group by pt.personid
having min(pt.type) = max(pt.type) and
       min(pt.type) = 1;