Who was called?

Question

I have mobile data with the persons Name, the date of the call, the from_number and the to_number. I need to have the association of the person's name that was called.

That is, if I have Barbara calling from number x to number y then I want to know to whom that y or to_number belongs to?

Below is an example data_set:

SELECT  'Barbara'   AS Person,  '2020-07-03'    AS Date,'022445544' AS From_Number, '022445545' AS To_Number UNION
SELECT  'Barbara'   AS Person,  '2020-07-03'    AS Date,'091234123' AS From_Number, '022445545' AS To_Number UNION
SELECT  'Peter' AS Person,  '2020-07-03'    AS Date,    '03123456'  AS From_Number, '08007543'  AS To_Number    UNION
SELECT  'Peter' AS Person,  '2020-07-03'    AS Date,    '03123456'  AS From_Number, '022445555' AS To_Number    UNION
SELECT  'Peter' AS Person,  '2020-07-04'    AS Date,    '022445545' AS From_Number, '091234123' AS To_Number    UNION
SELECT  'Peter' AS Person,  '2020-07-04'    AS Date,    '022445545' AS From_Number, '022445555' AS To_Number    UNION
SELECT  'Peter' AS Person,  '2020-07-04'    AS Date,    '022445545' AS From_Number, '049876543' AS To_Number    UNION
SELECT  'David' AS Person,  '2020-07-03'    AS Date,    '049876543' AS From_Number, '022445544' AS To_Number    UNION
SELECT  'David' AS Person,  '2020-07-04'    AS Date,    '022445555' AS From_Number, '022445545' AS To_Number   

I tried this:

select * 
   ,case when [From_Number] =[To_Number] then [Person] end as [from person]
   from [dbo].[cellphone] 

but I think the problem is that I don't know, how would you express that SQL needs to look for all possible numbers among one person of each person, because Barbara for example has many different phone numbers. So as if I would look for a range of numbers to pinpoint the caller number? Sometimes, of course we do not have any association as the number is unknown to us. This would then need to be noted as unknown number.

The desired dataset outcome should look like this:

SELECT  'Barbara'   AS Person,  '2020-07-03'    AS Date,'022445544' AS From_Number, '022445545' AS To_Number,'Peter'  AS person_called UNION
SELECT  'Barbara'   AS Person,  '2020-07-03'    AS Date,'091234123' AS From_Number, '022445545' AS To_Number, 'Peter'    AS person_called UNION
SELECT  'Peter' AS Person,  '2020-07-03'    AS Date,    '03123456'  AS From_Number, '08007543'  AS To_Number, 'unkn'     AS person_called UNION
SELECT  'Peter' AS Person,  '2020-07-03'    AS Date,    '03123456'  AS From_Number, '022445555' AS To_Number,   'David'  AS person_called UNION
SELECT  'Peter' AS Person,  '2020-07-04'    AS Date,    '022445545' AS From_Number, '091234123' AS To_Number,   'Barbara'AS person_called UNION
SELECT  'Peter' AS Person,  '2020-07-04'    AS Date,    '022445545' AS From_Number, '022445555' AS To_Number,   'David'  AS person_called UNION
SELECT  'Peter' AS Person,  '2020-07-04'    AS Date,    '022445545' AS From_Number, '049876543' AS To_Number,   'David'  AS person_called UNION
SELECT  'David' AS Person,  '2020-07-03'    AS Date,    '049876543' AS From_Number, '022445544' AS To_Number,   'Barbara'AS person_called UNION
SELECT  'David' AS Person,  '2020-07-04'    AS Date,    '022445555' AS From_Number, '022445545' AS To_Number ,  'Peter'  AS person_called 


I also tried this : 



   SELECT A.Person, A.Date, A.From_Number, A.To_Number      
    ,CASE WHEN EXISTS (SELECT B.Person, B.Date, B.From_Number, B.To_Number FROM [dbo].[mobile_data] B 
                        WHERE B.From_Number = A.To_Number)
    THEN B.Person = A.Person    ELSE 'Unknown'   END as caller_id
   from [dbo].[mobile_data] a

But I get the following Error MSG: The multi-part identifier "B.Person" could not be bound. Essentially I need : THEN B.Person ELSE But even if I try this It says that B.Person cannot be bound. Where is the syntax mistake?

Answer 1

Let me change the format of your query so it's a bit easier for me to add comments in useful spots.

  select A.Person, 
         A.Date, 
         A.From_Number, 
         A.To_Number,
         case
            when exists 
            (
               select   B.Person, B.Date, B.From_Number, B.To_Number 
               from     [dbo].[mobile_data] B 
               where    B.From_Number = A.To_Number
               -- the bit after "then" must  be the value you want if the "when" condition is true.
               -- the expression "b.person = a.person" isn't a value, it's either a check whether two things are equal,
               -- or an assignment from the thing on the right hand side to the thing on the left hand side
            ) then B.Person = A.Person 
            else 'Unknown'  
          end as caller_id
   -- This "from" clause only includes the alias "a". There's no "b" alias here. 
   -- So you can't refer to anything from "b" in this outer select.
   -- "b" only lives inside the "exists" condition above, it can't be seen from out here
   -- but you referred to b outside the "exists" condition, in the "then B.Person = A.Person ". 
   -- That's why SQL is saying that the "b.person" identifier could not be bound (it can't see it out here).
   from [dbo].[mobile_data] a

In order to answer your question in the way I think you want, we have to make an assertion: A single person can have many phone numbers, but a single phone number can only belong to one person.

If this is correct, we can take those calls and join them back onto themselves to get the answer. We will take the callers to_number, and try to match that to someone else's from_number. If we don't get a match, then the person being called has not themselves made any calls, so we don't know who they are.

We have to use distinct as well, because a person can make multiple calls. For example, Barbara called the number 022445544, which belongs to Peter. But peter has made 4 calls, so we'll match Barbara's call to all of those calls peter made. But of course, in all 4 of those calls it is Peter who is making it, so if we use distinct we'll just get one Peter, which is what we want.

create table calls 
(
   from_name varchar(32),
   call_date date,
   from_number varchar(16),
   to_number varchar(16)
);

insert   calls
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;            

Answer 2

WITH
cellphone
AS
(
   SELECT tbl.* FROM (VALUES
    ( 'Barbara', '03-Jul-2020', '022445544', '022445545')
   , ( 'Barbara', '03-Jul-2020', '091234123', '022445545')
   , ( 'David', '03-Jul-2020', '049876543', '022445544')
   , ( 'David', '04-Jul-2020', '022445555', '022445545')
   , ( 'Peter', '03-Jul-2020', '03123456', '022445555')
   , ( 'Peter', '03-Jul-2020', '03123456', '08007543')
   , ( 'Peter', '04-Jul-2020', '022445545', '022445555')
   , ( 'Peter', '04-Jul-2020', '022445545', '049876543')
   , ( 'Peter', '04-Jul-2020', '022445545', '091234123')
   ) tbl ([Person], [Date], [From_Number], [To_Number]) 
)
, 
person_cellphone
AS
(
   SELECT DISTINCT 
        [Person]
      , [From_Number]
   FROM 
      cellphone
)
SELECT 
     cel.[Person]
    , cel.[Date]
    , cel.[From_Number]
    , cel.[To_Number]
    , [To_Number] = ISNULL(pct.[Person], '<N/A>')
FROM 
   cellphone AS cel
   LEFT JOIN person_cellphone AS pct ON pct.[From_Number] = cel.[To_Number]

Answer 3

I used an online REPL and setup the table first.

CREATE TABLE people
( Person VARCHAR NOT NULL,
  Date VARCHAR NOT NULL,
  From_Number VARCHAR NOT NULL,
  To_Number VARCHAR NOT NULL
);
insert into people values('Barbara','2020-07-03','022445544','022445545');
insert into people values('Barbara','2020-07-03','091234123','022445545');
insert into people values('Peter','2020-07-03','03123456','08007543');
insert into people values('Peter','2020-07-03','03123456','022445555');
insert into people values('Peter','2020-07-04','022445545','091234123');
insert into people values('Peter','2020-07-04','022445545','022445555');
insert into people values('Peter','2020-07-04','022445545','049876543');
insert into people values('David','2020-07-03','049876543','022445544');
insert into people values('David','2020-07-04','022445555','022445545');

Then, used the following query.

SELECT *,
    CASE WHEN EXISTS (SELECT * FROM people B WHERE B.From_Number = A.To_Number)
        THEN (SELECT Person FROM people B WHERE B.From_Number = A.To_Number)
        ELSE 'Unknown'
    END Person_Called
FROM people A

That gave me the following result.

Barbara|2020-07-03|022445544|022445545|Peter
Barbara|2020-07-03|091234123|022445545|Peter
Peter|2020-07-03|03123456|08007543|Unknown
Peter|2020-07-03|03123456|022445555|David
Peter|2020-07-04|022445545|091234123|Barbara
Peter|2020-07-04|022445545|022445555|David
Peter|2020-07-04|022445545|049876543|David
David|2020-07-03|049876543|022445544|Barbara
David|2020-07-04|022445555|022445545|Peter