-1

The employeenumber data in the employeestable is similar to the salesrepemployeenumber in the customers tablecustomers table.

Some employees are not in the customers table.

How do I find the employees who are not in the salesrepemployeenumber row but presnt as employeenumber in the employees table .

note the data in the two rows is the same . and right and left join are not working

this is my code

select e.firstname, e.lastname, e.employeeNumber
from employees e 
    right join customers c on e.employeeNumber=c.salesRepEmployeeNumber 
where c.salesrepemployeeNumber is null
lak
  • 13
  • 2
  • What is your query returning? IE: what data is there that should not be there, and/or what data is *not* there that *should* be? – Mark Stewart Feb 25 '20 at 16:44
  • "and right and left join are not working" - Prove it by providing sample data and expected result. This has been asked many times.. See: [sql-find-records-from-one-table-which-dont-exist-in-another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) – Paul Spiegel Feb 25 '20 at 16:52
  • I am getting 22 rows of null columns – lak Feb 25 '20 at 16:53

2 Answers2

1

Try using left join 

select e.firstname, e.lastname, e.employeeNumber
from employees e 
LEFT join customers c on e.employeeNumber=c.salesRepEmployeeNumber 
where c.salesrepemployeeNumber is null
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • 1
    what do you mean with "not working" ... you have an error ?? show the exact error message ... wrong result ?? .. add a proper data sample .. the expected result and your actual result .. – ScaisEdge Feb 25 '20 at 16:57
  • @lak . . . This is a correct answer to your question. If it doesn't work for you, then either you did not ask the question you intend or there is operator error. – Gordon Linoff Feb 25 '20 at 17:31
0

If I understood correctly, you need employees which are not in customers table as salesRep. How about the following?

select e.firstname, e.lastname, e.employeeNumber
from employees e 
where e.employeeNumber not in (select c.salesRepEmployeeNumber from customers c)
silviagreen
  • 1,679
  • 1
  • 18
  • 39
  • it worked, the problem was I was not looking at employees who were not sales reps like managers – lak Feb 25 '20 at 17:42