CODEWITHSUNDEEP
mysqlsqljoinouter-join
Heena Tabassum
Heena Tabassum

Reputation: 128

Sql query to join 2 tables and count the values matching and non matching rows

I have two tables Employee and Department. I want to write a query that can give me data from both the tables with the count of the values.

    create table Employee(EmpID text);
    insert into Employee values("A");
    insert into Employee values("B");
    insert into Employee values("C");

    create table Departments(EmpID text);
    insert into Departments values("B");
    insert into Departments values("C");
    insert into Departments values("D");

OUTPUT

| EMP_ID    | COUNT     |
|--------   |-------    |
| A         | 1         |
| B         | 2         |
| C         | 2         |
| D         | 1         |

Searched everywhere, but didn't find anything helpful yet. Here is the playground https://paiza.io/projects/TdkdHannoclhbevdqpFlKw?language=mysql

Below is the query I m trying out, using the full outer join as it gives all matching and unmatching rows

SELECT *FROM Employee outer join Departments on Employee.EmpID=Departments.EmpID

Upvotes: 1

Views: 1095

Answers (3)

Felix
Felix

Reputation: 1810

You can use UNION ALL

see: here

or you can look into (temporary) merged tables

see: here

Upvotes: 0

J M
J M

Reputation: 1

select empid, count(empid)
from (
      select empid from employee 
      union all
      select empid from department
     ) e
group by empid;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269733

This is quite a strange data model. But use union all and then group by:

select empid, count(*)
from (select empid from employee union all
      select empid from department
     ) e
group by empid;

That you have empid in department that does not match empids in employee is a data modeling problem. You should have a foreign key relationship from department to employee to ensure that this can never happens. Your database lacks relational integrity.

Upvotes: 1

Related Questions