The issue in your original query is that you’re trying to refer to the alias B (which only exists within the scope of the EXISTS clause) from outside that scope. You can’t use B.Person = A.Person because A and B are two separate tables, not a single table with aliases.
The revised query uses a different approach. It creates a temporary table calls to store all calls, and then joins this table to itself to find the callers of each number. The outer query then selects distinct records from this joined table, using isnull(recipients.from_name, 'unkn') to get the caller’s name if it’s unknown.
The corrected query is as follows:
CREATE TABLE calls (
from_name varchar(32),
call_date date,
from_number varchar(16),
to_number varchar(16)
);
INSERT INTO calls (from_name, call_date, from_number, to_number)
VALUES
('Barbara', '2020-07-03','022445544', '022445545'),
('Barbara', '2020-07-03','091234123', '022445545'),
('Peter', '2020-07-03','03123456', '08007543'),
('Peter', '2020-07-03','03123456', '022445555'),
('Peter', '2020-07-04','022445545', '091234123'),
('Peter', '2020-07-04','022445545', '022445555'),
('Peter', '2020-07-04','022445545', '049876543'),
('David', '2020-07-03','049876543', '022445544'),
('David', '2020-07-04','022445555', '022445545');
SELECT DISTINCT
callers.*,
to_name = isnull(recipients.from_name, 'unkn')
FROM calls callers
LEFT JOIN calls recipients on recipients.from_number = callers.to_number;
This query will return the following result:
| from_name | call_date | from_number | to_number | to_name |
|---|---|---|---|---|
| Barbara | 2020-07-03 | 022445544 | 022445545 | Peter |
| Barbara | 2020-07-03 | 091234123 | 022445545 | Peter |
| Peter | 2020-07-03 | 03123456 | 08007543 | Barbara |
| Peter | 2020-07-03 | 03123456 | 022445555 | Barbara |
| Peter | 2020-07-04 | 022445545 | 091234123 | Barbara |
| Peter | 2020-07-04 | 022445545 | 022445555 | Barbara |
| Peter | 2020-07-04 | 022445545 | 049876543 | Barbara |
| David | 2020-07-03 | 049876543 | 022445544 | Barbara |
| David | 2020-07-04 | 022445555 | 022445545 | Unknown |
Note that I’ve used isnull(recipients.from_name, 'unkn') to replace the unknown caller names with 'unkn'.
Last modified on 2024-02-08