Join of three different mysql queries tables

Question

I want to join three different queries. Here are 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.

@reds,This is the result i am looking for user_name, first_name,lead_count,avgdays. If i excute this SELECT u.user_name,u.first_name FROM users u join users_cstm uc on u.id=uc.id_c WHERE u.deleted=0, i am getting the result of user_name, first_name. — jvk, Oct 20 '16 at 03:24
@reds. I don't no exact how to concatenate in mysql of three different query. Please help me. — jvk, Oct 20 '16 at 03:30

score 0 · Answer 1 · answered Oct 20 '16 at 03:33

0

Try this:

SELECT id,user_name,first_name,lead_count,avgdays from
(SELECT id,user_name,first_name,lead_count from
(SELECT u.id,u.user_name,u.first_name FROM users u,users_cstm uc where  u.id=uc.id_c and u.deleted=0) as a

LEFT JOIN

(SELECT l.assigned_user_id,count(*) AS lead_count FROM lead l GROUP BY l.assigned_user_id) as b
on a.id = b.assigned_user_id) as a

LEFT JOIN 

(SELECT l.assigned_user_id,AVG(DATEDIFF(l.date_modified,l.date_entered)) AS avgdays 
FROM leads l GROUP BY l.assigned_user_id) as b
on a.id = b.assigned_user_id

answered Oct 20 '16 at 03:33

Vijunav Vastivch

4,153
1
16
30

I am assuming id and assigned_user_id both relatively constraints. Im assuming its the same. you can change it if my guess is wrong. – Vijunav Vastivch Oct 20 '16 at 03:35
Thanks I will run this query. – jvk Oct 20 '16 at 04:00
I am not getting any result. The page is display blank. – jvk Oct 20 '16 at 05:15
add your table data here.. am i ryt id = assigned_user_id ? – Vijunav Vastivch Oct 20 '16 at 05:25

score 0 · Accepted Answer · answered Oct 20 '16 at 12:41

0

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;

answered Oct 20 '16 at 12:41

Rajdeep Kaur

16
1

Thanks you it's working. But i have more 10 queries with multiple conditions. and i have to join same way. – jvk Oct 20 '16 at 13:21
here are my different quries Here is the list. $sql_query2 = "SELECT l.assigned_user_id,count(*) as lead_count FROM leads l group by l.assigned_user_id"; $sql_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"; $sql_query4 = "SELECT c.assigned_user_id,count(*) as calls_count FROM calls c group by c.assigned_user_id"; and so on. I want combine all these and get single solution to display the values http://stackoverflow.com/questions/16364187/combining-2-sql-queries-and-getting-result-set-in-one – jvk Oct 20 '16 at 13:28

Join of three different mysql queries tables

2 Answers2