CODEWITHSUNDEEP
mysqlsqljoin
Prateek
Prateek

Reputation: 53

SQL Query to join two tables and filter the rows from first without corresponding entry in second

I have 2 tables in Mysql Database: One is Employee table to store employee details: employee

emp_id name
1       ABC   
2       XYZ   
3       EFG

Second is for monthly wages: wages

 wage_id  emp_id month year   wage   paid
 1        1      11    2015   2000   2000
 2        2      11    2015   1000   1000
 3        3      11    2015   1500   1700
 4        1      12    2015   2000   2000
 5        3      12    2015   1500   0

I have to filter those employees whose wages are not entered for the month or whose paid is less or more than wage. For Ex.

When Month=11

Name  Wage Paid
EFG   1500 1700

And when Month=12

Name  Wage  Paid
XYZ   0     0
EFG   1500  0

I tried several things but couldn't get desired output.

Select  employee.emp_id, 
        employee.name, 
        wages.emp_id, 
        wages.wage_id, 
        wages.wage, 
        wages.paid 
From    employee 
Join    wages       On  employee.emp_id = wages.emp_id 
Where   wages.month = 11 
And     wages.year = 2015 
And     wages.wage <> wages.paid

And

Select  wages.wage, 
        wages.paid, 
        wages.emp_id, 
        employee.emp_id 
From    wages 
Join    employee    On  wages.emp_id = employee.emp_id 
Where   wages.month = 11 
And     wages.year = 2015 
And     wages.wage <> wages.paid

Please suggest changes.

Upvotes: 0

Views: 57

Answers (3)

Sudhir Panda
Sudhir Panda

Reputation: 784

create table Employee( emp_id int not null Primary key,
name varchar(50) null
);

insert into Employee values(1,'ABC')
insert into Employee values(2,'XYZ')
insert into Employee values(3,'EFG')

Create table Wages (
wage_id int not null primary key,
emp_id int null,
month int null,
year int null,
wage int    null,
paid int null
)


 insert into wages values(1,1,11,2015,2000,2000)
 insert into wages values(2,2,11,2015,1000,1000)
 insert into wages values(3,3,11,2015,1500,1700)
 insert into wages values(4,1,12,2015,2000,2000)
 insert into wages values(5,3,12,2015,1500,0   )

-------------Query to get the desired output-----------
SELECT e.Name, ISNULL(w.wage, 0), ISNULL(w.paid, 0)
FROM Employee e
LEFT JOIN Wages w
ON e.emp_id = w.emp_id 
AND [month] = 11 AND [YEAR] = 2015
WHERE w.wage IS NULL OR w.wage <> w.paid

try this

Upvotes: 0

John Paul
John Paul

Reputation: 837

This should work: Just change [month] to whatever you want.

SELECT e.Name, IFNULL(w.wage, 0), IFNULL(w.paid, 0)
FROM Employees e
LEFT JOIN Wages w
ON e.emp_id = w.emp_id AND [month] = 12 AND [YEAR] = 2015
WHERE w.wage IS NULL OR w.wage <> w.paid

Upvotes: 1

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

  1. Make outer join on month, year and emp_id to get missing rows in wage table: select e.name, ifnull(w.wage,0) as wage, ifnull(w.paid,0) as paid from employee e left join wage w on w.year=2015 and w.month=11 and w.emp_id=e.emp_id
  2. Take rows where wage is null or unequal paid: where w.wage is null or w.wage<>w.paid

Upvotes: 0

Related Questions