I am trying to get data using 4 tables. List of Tables
I want to fetch data no of calls's Number attached with specific labels. Here is table.
Number Table :
select id,uuid,number from numbers limit 1;
+----+--------------------------------------+------------+
| id | uuid | number |
+----+--------------------------------------+------------+
| 1 | ed268b05-758e-44fd-b429-8d5223651814 | 1234561222 |
+----+--------------------------------------+------------+
Call Table
mysql> select id,uuid,did from calls limit 2;
+----+-------------------------------------+------------+
| id | uuid | number |
+----+-------------------------------------+------------+
| 1 | ddddass-b810-4f23-7456-8ff56efab080 | 1234561222 |
| 2 | dddddd-b810-4f23-7456-8ff56efab080 | 123456789 |
+----+-------------------------------------+------------+
2 rows in set (0.02 sec)
ref_label_no Table
mysql> select uuid,number_uuid,label_uuid from ref_label_no limit 1;
+--------------------------------------+--------------------------------------+--------------------------------------+
| uuid | number_uuid | label_uuid |
+--------------------------------------+--------------------------------------+--------------------------------------+
| 7a5e1b6e-0194-4993-8d78-5f7a1b60c7d2 | ed268b05-758e-44fd-b429-8d5223651814 | 7262b06e-9263-4825-8411-4c107104a60b |
+--------------------------------------+--------------------------------------+--------------------------------------+
1 row in set (0.00 sec)
labels table
mysql> select uuid,name from labels limit 1;
+--------------------------------------+-------+
| uuid | name |
+--------------------------------------+-------+
| 7262b06e-9263-4825-8411-4c107104a60b | INDIA |
+--------------------------------------+-------+
1 row in set (0.00 sec)
Now I want result like Calls number with total calls.[This Output i needed]
mysql> select uuid,name from labels limit 1;
+--------------------------------------+-------+
| Number | Total_calls |
+--------------------------------------+-------+
| 13456789 | 2 |
+--------------------------------------+-------+
1 row in set (0.00 sec)
I have made below query but no success also taking too much time to response.
select count(*) as Total_calls,calls.number as did,labels.name,calls.created_at as callstart from calls, labels JOIN ref_label_no ON labels.uuid = ref_label_no.label_uuid JOIN numbers ON numbers.uuid = ref_label_no.number_uuid where labels.name="INDIA" group by calls.number;
Any Suggestion Or recommendation ?
Multiple tables Avoid using join, use Union. It will be faster than join.
Like this,
select uuid,did from calls union
select uuid,number_uuid as did from ref_label_no union
select uuid,name as did from labels
SELECT COUNT(calls.uuid) as total_calls, numbers.number
FROM numbers JOIN calls ON numbers.number = calls.number
JOIN ref_label_no ON ref_label_no.number_uuid = numbers.uuid
JOIN labels ON labels.uuid = ref_label_no.label_uuid
WHERE labels.name="INDIA"
GROUP BY numbers.number;
Since we are calculating the total count of calls grouping by numbers, you won't have the ability to select calls.created_at in the query result because it conflicts with the aggregation function COUNT.
The following query use EXISTS condition:
SELECT COUNT(calls.uuid) as total_calls, numbers.number
FROM calls JOIN numbers ON numbers.number = calls.number
WHERE EXISTS(SELECT 1 FROM ref_label_no JOIN labels ON labels.uuid = ref_label_no.label_uuid
WHERE labels.name="INDIA" AND numbers.uuid = ref_label_no.number_uuid)
GROUP BY numbers.number;