How can I make this select lookup another table and find first match?

Question

I have a database of phone call data from our phone system that I am trying to create a report on. These phone calls match up to a table of internal and external numbers. The report needs to try to match the phone call to an external number in our database first and if there is no match try to match it to an internal number.

I have created a sample data set and db-fiddle, and removed some data to hopefully explain it better:

CREATE TABLE `cdr` (
  `callnumber` int(11) NOT NULL,
  `origLegCallIdentifier` int(11) NOT NULL,
  `dateTimeOrigination` datetime NOT NULL,
  `callType` varchar(50) NOT NULL,
  `chargeable` varchar(10) NOT NULL,
  `callCharge` decimal(10,2) NOT NULL,
  `origNodeId` int(11) NOT NULL,
  `destLegIdentifier` int(11) NOT NULL,
  `destNodeId` int(11) NOT NULL,
  `callingPartyNumber` varchar(50) NOT NULL,
  `callingPartyNumberPartition` varchar(50) NOT NULL,
  `callingPartyNumberState` varchar(10) NOT NULL,
  `callingPartyNumberSite` varchar(30) NOT NULL,
  `originalCalledPartyNumber` varchar(50) NOT NULL,
  `originalCalledPartyNumberPartition` varchar(50) NOT NULL,
  `finalCalledPartyNumber` varchar(50) NOT NULL,
  `finalCalledPartyNumberPartition` varchar(50) NOT NULL,
  `lastRedirectDn` varchar(50) NOT NULL,
  `lastRedirectDnPartition` varchar(50) NOT NULL,
  `dateTimeConnect` datetime DEFAULT NULL,
  `dateTimeDisconnect` datetime NOT NULL,
  `duration` int(11) NOT NULL,
  `origDeviceName` varchar(129) NOT NULL,
  `destDeviceName` varchar(129) NOT NULL,
  `origIpv4v6Addr` varchar(64) NOT NULL,
  `destIpv4v6Addr` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `cdr` (`callnumber`, `origLegCallIdentifier`, `dateTimeOrigination`, `callType`, `chargeable`, `callCharge`, `origNodeId`, `destLegIdentifier`, `destNodeId`, `callingPartyNumber`, `callingPartyNumberPartition`, `callingPartyNumberState`, `callingPartyNumberSite`, `originalCalledPartyNumber`, `originalCalledPartyNumberPartition`, `finalCalledPartyNumber`, `finalCalledPartyNumberPartition`, `lastRedirectDn`, `lastRedirectDnPartition`, `dateTimeConnect`, `dateTimeDisconnect`, `duration`, `origDeviceName`, `destDeviceName`, `origIpv4v6Addr`, `destIpv4v6Addr`) VALUES
(52004, 69637277, '2020-08-31 03:05:05', 'outbound-national', 'yes', '0.00', 4, 69637278, 4, '6220', 'PT_INTERNAL', 'NSW', 'Site A', '0412345678', 'PT_NATIONAL_TIME_RESTRICT', '0412345678', 'PT_NATIONAL_TIME_RESTRICT', '0412345678', 'PT_NATIONAL_TIME_RESTRICT', NULL, '2020-08-31 03:05:08', 0, 'SEP00XXXXX', 'XXXXX', '1.1.1.1', '1.1.1.1');

CREATE TABLE `numbers` (
  `numberid` int(11) NOT NULL,
  `number` varchar(30) NOT NULL,
  `memberid` int(11) NOT NULL,
  `type` enum('internal','external') NOT NULL,
  `description` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `numbers` (`numberid`, `number`, `memberid`, `type`, `description`) VALUES
(1555, '0412345678', 436, 'internal', ''),
(1556, '6220', 437, 'external', '');

https://www.db-fiddle.com/f/ofH6sENoce8tGVsoxMejwZ/1

The above example shows how it ends up with a duplicate for a single record because it matches 6220 as the callingPartyNumber and 0412345678 as the finalCalledPartyNumber in each respective select.

This is an example of what I want to see (union has been removed): https://www.db-fiddle.com/f/bVSWESvnKJKvuNefLqH4aU/0

I want a single record for when it either matches a finalCalledPartyNumber first or then a callingPartyNumber. Records that don't match anything will not be shown.

Updated select using Caius's example

SELECT
DATE(CONVERT_TZ(cdr.dateTimeOrigination,'+00:00',@@global.time_zone)) as 'Date',
TIME(CONVERT_TZ(cdr.dateTimeOrigination,'+00:00',@@global.time_zone)) as 'Time',
cdr.callType,
cdr.callingPartyNumberState, 
cdr.callingPartyNumber,
COALESCE(finalcalledparty.memberid, callingparty.memberid, originalcalledparty.memberid, 'No Match') as MemberID,
cdr.originalCalledPartyNumber,
cdr.finalCalledPartyNumber, 
CONCAT(MOD(HOUR(SEC_TO_TIME(cdr.duration)), 24), ':', LPAD(MINUTE(SEC_TO_TIME(cdr.duration)),2,0), ':', LPAD(second(SEC_TO_TIME(cdr.duration)),2,0)) as 'duration', 
cdr.callCharge 
FROM `cdr`
LEFT JOIN numbers finalcalledparty ON finalcalledparty.number = cdr.finalCalledPartyNumber
LEFT JOIN numbers callingparty ON callingparty.number = cdr.callingPartyNumber
LEFT JOIN numbers originalcalledparty ON originalcalledparty.number = cdr.OriginalCalledPartyNumber
WHERE (cdr.callType LIKE '%outbound%' OR cdr.callType LIKE '%transfer%' OR cdr.callType LIKE '%forward%')
ORDER BY Date DESC, Time DESC

Select with members table join

SELECT
DATE(CONVERT_TZ(cdr.dateTimeOrigination,'+00:00',@@global.time_zone)) as 'Date',
TIME(CONVERT_TZ(cdr.dateTimeOrigination,'+00:00',@@global.time_zone)) as 'Time',
cdr.callType,
'Calling' as ChargeType,
cdr.callingPartyNumberState, 
cdr.callingPartyNumber,
COALESCE(finalcalledmember.name, callingmember.name, 'No Match') as MemberName,
cdr.finalCalledPartyNumber, 
CONCAT(MOD(HOUR(SEC_TO_TIME(cdr.duration)), 24), ':', LPAD(MINUTE(SEC_TO_TIME(cdr.duration)),2,0), ':', LPAD(second(SEC_TO_TIME(cdr.duration)),2,0)) as 'duration', 
cdr.callCharge 
FROM `cdr`
LEFT JOIN numbers callingparty ON callingparty.number = cdr.callingPartyNumber
LEFT JOIN numbers finalcalledparty ON finalcalledparty.number = cdr.finalCalledPartyNumber
LEFT JOIN members callingmember ON callingmember.memberid = callingparty.memberid
LEFT JOIN members finalcalledmember ON finalcalledmember.memberid = finalcalledparty.memberid
WHERE (callType LIKE '%outbound%' OR callType LIKE '%transfer%' OR callType LIKE '%forward%') AND DATE(CONVERT_TZ(cdr.dateTimeOrigination,'+00:00',@@global.time_zone)) = '2020-09-01'
ORDER BY Date DESC, Time DESC

user_1410664 · Answer

The report needs to try to match the phone call to an external number in our database first and if there is no match try to match it to an internal number.

You can use a pair of left joins for this. Here's a simpler dataset:

Person, Number
John, e1
James, i2
Jenny, x3

ExternalNumber, Message
e1, Hello

InternalNumber
i2, Goodbye


SELECT p.Person, COALESCE(e.Message, i.Message, 'No Match')
FROM
  Person p
  LEFT JOIN Externals e ON p.Number = e.ExternalNumber
  LEFT JOIN Internal e ON p.Number = i.InternalNumber

Results:

John, Hello
James, Goodbye
Jenny, No Match

Few things you need to appreciate about SQL in general:

A UNION makes a dataset grow taller (more rows)
A JOIN makes a dataset grow wider (more columns)
It is easy to compare things on the same row, more difficult to compare things on different rows
There isn't exactly a concept of "doing something now" and "doing something later" - i.e. your "try to match it to external first and if that doesn't work try match it to internal" isn't a good way to think about the problem, mentally. The SQL way would be to "match it to external and match it to internal, then preferentially pick the external match, then the internal match, then maybe no match"
COALESCE takes a list of arguments and, working left to right, returns the first one that isn't null. Coupled with LEFT JOIN putting nulls when the match fails, it means we can use it to prefer external matches over internal

Because it's easier to compare things on the same row, we just try and match the data against the external and internal numbers tables as a direct operation. We use LEFT JOIN so that if the match doesn't work out, at least it doesn't cause the row to disappear..

So you join both numbers tables in and the matches either work out for external (and you will pick external), work out for internal but not external (and you will pick internal), work out for both int and ext (and you will pick ext over int), or don't work out (and you might have a message to say No Match)

It should be pointed out that the COALESCE approach only really works well if the data won't naturally contain nulls. If the data looked like this:

Person, Number
John, e1
James, i2
Jenny, x3

ExternalNumber, Message
e1, NULL

InternalNumber
i2, Goodbye

Then this will be the result:

John, Goodbye
James, Goodbye
Jenny, No Match

Even though the join succeeded, the presence of a NULL in the ExternalNumber.Message means the InternalNumber.Message is used instead, and this might not be correct. We can solve this by using CASE WHEN instead, to test for a column that definitely won't be null when a record matches:

CASE 
  WHEN e.ExternalNumber IS NOT NULL THEN e.Message 
  WHEN i.InternalNumber IS NOT NULL THEN i.Message
  ELSE 'No Match'
END

Because we test the column that is the key for the join the only way we can get a null there is when the join fails to find a match.

How can I make this select lookup another table and find first match?

1 Answers