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.

---

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.

---

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).

---

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.

---

left outer join if you are joining from contracts to salesman

edit: had order around the wrong way

---

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)

---

I suppose this is the answer :

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