Ndtyjky

Here I have two tables each have supervision field and user_id field. I want groupwise count for each supervision for both the table and print as below. both the tables contain different data.

Also count should be for specified user_id only.

Table1 columns

+----+-------------+---------+

| id | supervision | user_id |

+----+-------------+---------+

|  1 | type1       |       2 |

|  2 | type1       |       2 |

|  3 | type2       |      1  |

|  4 | type1       |       2 |

|  5 | type2       |       2 |

+----+-------------+---------+

Table2 columns

+----+-------------+---------+

| id | supervision | user_id |

+----+-------------+---------+

|  1 | type3       |       2 |

|  2 | type1       |       2 |

|  3 | type3       |       1 |

|  4 | type1       |       2 |

|  5 | type2       |       2 |

+----+-------------+---------+

For user_id=2 it should give output like this:

+-------+--------+--------+

| Type  | table1 | table2 |

+-------+--------+--------+

| type1 |      3 |      2 |

| type2 |      1 |      1 |

| type3 |      0 |      1 |

| ....  |        |        |

+-------+--------+--------+

For now this query I tried it gives correct result for table1 for table2 not.

select t1.supervision,

       count(t1.supervision) AS table1,

       count(t2.supervision) AS table2 from table1 t1 

LEFT JOIN 

table2 t2 ON t1.id=t2.id 

where t1.userid=2 AND t2.userid=2  

group by t1.supervision

SELECT t1.supervision, count(t1.id) AS table1, count(t2.id) AS table2

FROM users u

LEFT JOIN table1 t1 on (t1.user_id = u.id)

LEFT JOIN table2 t2 on (t2.user_id = u.id)

WHERE u.id = 2

GROUP BY t1.supervision

This assumes the users table exists. The problem you had was type3 did not exits in t1 so the left join would not have included values from there, and you were joining on id instead of user_id.