SQL Comparing two attributes as 1 combination

Question

I am student, new to database programming and new to Stack Overflow!

Basically I have a database which has:

Table => Attributes

Mine => MineID, Name, NumberOfWorkers
Ore => OreID, Name, ChemicalFormula                 
OreProduction => OreProdID, Mine, Ore, AmountPerYear
Contract = > ContractID, Ore, Mine, Smelter, AmountOfOre

There are other tables but I think these are those relevant to my problem.

Now my Question is :

"Which mines have the capacity to mine an ore that they have not yet contracted to a smelter, and what Ore(s) are they?"

Now this gives me all the mines that can mine an ore AND have it is contracted to a smelter

SELECT DISTINCT Mine.Name, Ore.Name
FROM OreProduction
INNER JOIN Mine
ON Mine.MineID = OreProduction.Mine
INNER JOIN Ore
ON Ore.OreID = OreProduction.Ore
INNER JOIN ContractDetail
ON OreProduction.Mine = ContractDetail.Mine
AND OreProduction.Ore = ContractDetail.Ore

How can I get the inverse for this ? I've tried to use this as a sub query but how can I tell SQL that Mine and Ore together NOT IN the above sub query ?

I hope you've understood what I'm trying to say and thanks in advance for your replies

Answer 1

Try a LEFT JOIN and use a WHERE clause:

SELECT DISTINCT Mine.Name, Ore.Name
FROM OreProduction
INNER JOIN Mine
ON Mine.MineID = OreProduction.Mine
INNER JOIN Ore
ON Ore.OreID = OreProduction.Ore
LEFT JOIN ContractDetail
ON OreProduction.Mine = ContractDetail.Mine
AND OreProduction.Ore = ContractDetail.Ore
WHERE Contract.Mine is null

Answer 2

An INNER JOIN will only return rows that meet the join conditions, and leave out any rows from either table that don't match.

A LEFT or RIGHT join will return all the data from one table (the left or right), and matching data from the other table. If there are no rows that match, then the columns are NULL in the output.

So, a hint would be: try doing a LEFT OUTER JOIN from Ore to ContractDetail, and then filtering the result of that with a WHERE clause that looks specifically for "null" in the contract details.