How to select an id when we do not have matching records in Teradata

Say i have two tables like below:

+-------------------------+   +-------------------------+
| Person                  |   | Person Details          |
+----------+--------------+   +-------------------------+
| Name     |VARCHAR(255)  |   | SK       | Decimal(18,0)|
| PersonID | INT(11)      |   | PersonID | INT(11)      |
+----------+--------------+   | Name     | VARCHAR(255) |
                              |                         |
                              +----------+--------------+

My tables contain following data:

+------------------------+    +---------------------------+
| Person                 |    | Person Details            |
+----------+-------------+    +-------+----------+--------+
| Name     | PersonID    |    | SK    | PersonID | Name   |
+----------+-------------+    +-------+----------+--------+
| Sean     | 1           |    | 1     | 1        | Sean   |
| Javier   | 1           |    | 2     | 1        | Cooper |
| Fluffy   | 2           |    | 3     | 2        | Trade  |
+----------+-------------+    +-------+----------+--------+

I need to select PersonId from Person table where all Name associated with an Id should not be in Person Details table. In above example my query should return only 2 not PersonID 1 as “Sean” is found in both Person and Person_details table. I tried below query and did not work:

sel P.PersonID
from Person P
left join Person_details PD
on PD.PersonId = P.PersonId
and PD.Name = P.Name

where PD.PersonId is NULL

Can anyone please help.

Thanks in advance.

Answer

Your current query returns all PersonIDs where any name is missing.

To get those IDs where all names are missing you need aggregation:

SELECT P.PersonID
FROM Person P
LEFT JOIN Person_details PD
  ON PD.PersonId = P.PersonId
 AND PD.Name = P.Name
GROUP BY p.PersonID             -- for each PersonID
HAVING Max(pd.PersonID) IS NULL -- no match was found

Advertisement

Answer