whoami whoami - 1 month ago 7
MySQL Question

Join of three different mysql queries tables

Hai I am new to php and mysql. Here want to join three different quires.
Here is my queries

Query1:

SELECT u.user_name,u.first_name
FROM users u join users_cstm uc on u.id=uc.id_c
WHERE u.deleted=0?


Query2:

SELECT l.assigned_user_id,count(*) AS lead_count
FROM lead l GROUP BY l.assigned_user_id?


Query3:

SELECT l.assigned_user_id,AVG(DATEDIFF(l.date_modified,l.date_entered)) AS avgdays
FROM leads l GROUP BY l.assigned_user_id?


and so..on..

This is the result i am looking for user_name, first_name,lead_count,avgdays. from three tables.

BigQuery join of three tables

I have tried my solution from the above link. But I didn't get the result?

Thank you for the help and suggestions.

Answer

Try below query:-

SELECT u.user_name,u.first_name,
lead_table.lead_count,lead_table.avgdays
FROM users u join users_cstm uc on u.id=uc.id_c
left join 
(SELECT l.assigned_user_id,
        AVG(l.date_modified) AS avgdays,
        count(*) AS lead_count 
FROM leads l GROUP BY l.assigned_user_id) lead_table
on u.id=lead_table.assigned_user_id
WHERE u.deleted=0;