Outer Join and a two step query

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

Hi,
I need to list which dog breeds have never been sold at the Store by using
Outer Join and a two step query. Anyone can explain for me Out join

Thank you all

My tables are as follows:
Table Animal has
AnimalID
Category
Breed

Table Customers
CustomerID
Phone
Name

Table Sales
CustomeriD
SaleDate
SaleID

Table Saleanimal
SaleID
AnimalID
SalePrice

Table SaleItem
SaleiD
ItemID
 
The following SQL should list which dogs have not been sold at the store:

SELECT Animal.AnimalID, Animal.Category, Animal.Breed
FROM Animal LEFT JOIN Saleanimal
ON Animal.AnimalID = Saleanimal.AnimalID
WHERE Saleanimal.AnimalID IS NULL

Another, less efficient, approach would be

SELECT AnimalID, Category, Breed
FROM Animal
WHERE AnimalID NOT IN
(SELECT DISTINCT AnimalID
FROM Saleanimal)
 
Back
Top