Finding Duplicate Entry from multiple table in mysql

Question

I have three tables having following structure

Table Name : users

id        name      age
1         Alok       26
2         Ashok      28
3         Amit       25

Table Name : Departments

id        name      d_name
1         Alok       Ops
2         Amit       IT
3         Shekahr    CS

I want duplicate name with total count as following using mysql query

   total     name
   2         Alok
   2         Amit
   1         Ashok
   1         Shekhar

Please help

Thanks in Advance.

Answer 1

Try this:

select count(*) as total,name  from (
select name from users
union 
all select * from deepartment ) as temp
group by name

Union all will merge your tables and with group by and count you should get the expected result.