How to add a column in select query with right join

Question

I'm trying to find a way to add a country code to a database call record based on a phone number column. I have a table that contains countries and their dialing codes (called countries). I can query all records and append the country code, but I need to be able to filter and paginate the results.

I'm working with a system that I don't have a lot of control over, so adding new columns to the table or rewriting large chunks of code aren't really options. That's what I have to deal with.

Country/region table.

id	Name	Dial Code
1	Ireland	353
2	USA	1

Call record table.

id	Start Date Time	End Date Time	route_id	telephone number	duration_seconds
1	2014-12-18 18:51:12	2014-12-18 18:52:12	twenty three	3538700000	60
2	2014-12-18 17:41:02	2014-12-18 17:43:02	43	18700000	120

Routing table.

id	number	activated
twenty three	1234567890	1
43	0987654321	1

I need to get the sum value of duration with all unique phone numbers grouped by route_id, route_number, but now we need to group these results by country_id so that we can group callers by country. I am using the below mysql query to get the sum value of duration, total number of unique phone numbers, all grouped by route_id, route_number. This query was written by another developer a long time ago.

SELECT 
    phone_number,
    route_number, 
    COUNT(callrecord_id) AS total_calls, 
    SUM(duration_sec) AS total_duration, 
    callrecord_join.route_id
FROM routes
RIGHT JOIN (
    SELECT 
        DATE(a.startdatetime) AS call_date, 
        a.id AS callrecord_id, 
        a.route_id AS route_id, 
        a.phonenumber AS phone_number,
        a.duration_seconds as duration_sec,
        b.inboundnumber AS route_number, 
    FROM callrecord AS a
    INNER JOIN routes AS b ON a.route_id = b.id
    WHERE DATE_FORMAT(a.startdatetime, '%Y-%m-%d') >= '2014-12-18' 
    AND DATE_FORMAT(a.startdatetime, '%Y-%m-%d') <= '2014-12-18' 
    AND b.isenabled = 1 
) AS callrecord_join ON routes.id = callrecord_join.route_id
GROUP BY route_id, route_number
LIMIT 10 offset 0;

I've done all the work of adding the country_id to the right join table so that I can group by country_id.

I know I can use php to iterate through each country and get the results using a where clause as shown below, but I can't paginate these results or filter them easily.

Left(a.phonenumber, strlen($dialling_code)) = $dialling_code

How do I add a column to a join table query containing country IDs using the country table so that I can group by route ID, route number, and country ID? As shown in the table below.

id	Start Date Time	End Date Time	route_id	telephone number	duration_seconds	country_id
1	2014-12-18 18:51:12	2014-12-18 18:52:12	twenty three	3538700000	60	1
2	2014-12-18 17:41:02	2014-12-18 17:43:02	43	18700000	120	2

Answer 1

The RIGHT JOIN from routes to callrecord_join does nothing because you already have the INNER JOIN subquery in between The routes and callrecord are located on the right side of the join.

You can use the join you described -

JOIN countries c ON LEFT(a.phonenumber, LENGTH(c.dialling_code)) = c.dialling_code

But it will give the same result:

JOIN countries c ON a.phonenumber LIKE CONCAT(c.dialling_code, '%')

It should be slightly cheaper.

You should test the inclusion of countries to ensure that any number in callrecord is not added to more than one country. Some international dialing codes are ambiguous, so it depends on which dialing code list you use.

SELECT a.*, COUNT(*), GROUP_CONCAT(c.dialling_code)
FROM callrecord a
JOIN country c ON a.phonenumber LIKE CONCAT(c.dialling_code, '%')
GROUP BY a.id
HAVING COUNT(*) > 1;

Obviously, if your dataset is very large, you will need to batch the above query.

I hope I'm not oversimplifying things, but from my understanding of your question, the query is just:

SELECT
    r.id AS route_id,
    r.number AS route_number,
    c.name AS country_name,
    SUM(a.duration_seconds) AS total_duration,
    COUNT(a.id) AS total_calls,
    COUNT(DISTINCT a.phonenumber) AS unique_numbers
FROM callrecord AS a
JOIN routes AS r ON a.route_id = r.id
JOIN countries c ON a.phonenumber LIKE CONCAT(c.dialling_code, '%')
WHERE a.startdatetime >= '2014-12-18' 
AND a.startdatetime < '2014-12-19'
AND r.isenabled = 1
GROUP BY r.id, r.number, c.name
LIMIT 10 offset 0;

Note that, assuming a suitable index is available, be careful to remove DATE_FORMAT() from startdatetime to make these conditions controllable.