CODEWITHSUNDEEP
mysqlsqljoin
bikey77
bikey77

Reputation: 6672

Which type of join do I need?

I have 2 tables, contracts and salesmen. Recently I've discovered some errors in the data, some contracts have salesid's not found in 'salesmen', I suspect an accidental deletion or an input error of some kind.

Which join should I use to find all contracts that dont 'belong' to a salesman, in other words, the contract.salesid not found in the salesmen.id column.

It should be a right outer join but the results arent coming up right.

Upvotes: 1

Views: 78

Answers (6)

ladenedge
ladenedge

Reputation: 13419

Sounds like you're looking for an "anti-join". Explain Extended talks about the three ways to do this in MySQL:

  • A left join with a WHERE __ IS NULL
  • A NOT IN clause with a subselect.
  • A NOT EXISTS clause with a subselect.

Upvotes: 4

Yordan Borisov
Yordan Borisov

Reputation: 1652

I suppose this is the answer :

select * from Contract c 
right outer join Salesmen s on (c.salesid = s.id)

Upvotes: 0

Blindy
Blindy

Reputation: 67380

An outer join could indeed do it, but why not simply:

select *
from contract c
where c.salesid not in (select s.id
                        from salesmen s)

Upvotes: 0

0xCAFEBABE
0xCAFEBABE

Reputation: 5666

SELECT c.contract_id FROM contract c
LEFT OUTER JOIN salesmen s ON s.salesman_id = c.salesman_id
WHERE c.salesman_id IS NULL

would be my guess.

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270617

If you're looking for contract.salesid not found in the salesmen.id, you can use NOT IN() rather than a join.

SELECT * FROM contracts WHERE salesid NOT IN (SELECT id FROM salesmen);

The above will return everything from contracts having salesid that matches no existing salesmen.id. Using NOT IN () on large tables can be slower than doing it with a JOIN, but if your tables aren't too large it is usually a more straightforward method to use (in my opinion).

Upvotes: 3

josh-fuggle
josh-fuggle

Reputation: 3157

left outer join if you are joining from contracts to salesman

edit: had order around the wrong way

Upvotes: 0

Related Questions