How to get rows with a specific count on two columns in addition to selecting a third column?-CodePudding

Lets say I joined two tables with this query:

    select e.fname, e.lname, d.name from employees e
left join Departments d on e.DepartmentId = d.Id;

And this was my result:

fname   lname   name
--------------------
James   Smith     HR
Michael Williams  HR
Michael Williams  Sales
John    Smith     HR

Now, I want to get all rows where fname and lname are equal and count= 2 but get/select the department name as well.

I can do it in two separate queries but I was wondering if it could be done in a single query.

First query where count is 2:

select count(*), concat(e.fname, e.lname)
from employees e
left join Departments d on e.DepartmentId = d.Id
group by concat(e.fname, e.lname)
having count(*) =2;

Result:

count(*)    concat(e.fname, e.lname)
------------------------------
2             MichaelWilliams

Second query:

select e.fname, e.lname, d.name from employees e
left join Departments d on e.DepartmentId = d.Id
where concat(e.fname, e.lname) = 'MichaelWilliams' ;

Result(this is what I'm trying to get in a single query):

    fname    lname     name
------------------------------
    Michael   Williams  HR
    Michael   Williams  Sales

Thanks in advance.

CodePudding user response：

you can use group_concat to get the employees who have multiple departments but this will return the data in one record

    fname    lname     name
------------------------------
    Michael   Williams  HR,Sales

select e.fname, e.lname, group_concat(d.name)
from employees e
left join Departments d on e.DepartmentId = d.Id
group by e.fname, e.lname
having count(*) > 1;

if you want the records to be on multiple records you will have to use a subquery

select e.fname, e.lname, d.name from employees e
left join Departments d on e.DepartmentId = d.Id
where concat(e.fname, e.lname) in (select concat(e.fname, e.lname)
from employees e
group by concat(e.fname, e.lname)
having count(*)>1);