CODEWITHSUNDEEP
mysql
Harry Forbess
Harry Forbess

Reputation: 2134

mysql show items that are not matched in second table.

I believe i understand joins, left, right innner but I am having a problem with this task. I have to show records that dont match in the second table.

Like if you are an employee, and the employee_phone table doesn't have a record matching you, what sort of query should I use?

I need to find all the employees who don't have a match in the employee_phone table.

Upvotes: 0

Views: 53

Answers (3)

user1836293
user1836293

Reputation: 148

select * from employees e where not exists (select 'x' from employee_phone ep where e.emp_id = ep.emp_id );

Basicly you want to do a not exists search in the employee_phone table joining them with the employee id (or other joining factor)

Need more information on the table structures to make it more accurate

Upvotes: 1

juergen d
juergen d

Reputation: 204756

select * 
from employee
where id not in (select emp_id from employee_phone)

or

select e.* 
from employee e
left outer join employee_phone ep on e.id = ep.emp_id
where ep.emp_id is null

Upvotes: 2

John Woo
John Woo

Reputation: 263723

query will look like this if you only want to show record that has no match on the other table.

SELECT  a.*
FROM    tableA a
        LEFT JOIN tableB b
            ON a.ID = b.ID
WHERE   b.ID IS NULL

Upvotes: 1

Related Questions