Dofactory.com
Dofactory.com

SQL JOIN 3 Tables

An SQL query can JOIN three tables (or more).

Simply add an extra JOIN condition for the third table.

3-Table JOINs work with SELECT, UPDATE, and DELETE queries.

Example

#

Problem: List all suppliers with products that have sold, sorted by supplier.

SELECT DISTINCT CompanyName, ProductName
  FROM Supplier S
  JOIN Product P ON S.Id = P.SupplierId
  JOIN OrderItem I ON P.Id = I.ProductId
 ORDER BY CompanyName

Note: This query returns 77 records.
Removing the second JOIN returns 78 records, because only 1 product never sold.

Result:  77 records
CompanyName ProductName
Aux joyeux ecclésiastiques Chartreuse verte
Aux joyeux ecclésiastiques Côte de Blaye
Bigfoot Breweries Laughing Lumberjack Lager
Bigfoot Breweries Sasquatch Ale
Bigfoot Breweries Steeleye Stout
Cooperativa de Quesos 'Las Cabras' Queso Cabrales

Syntax

3-Table JOIN syntax.

SELECT column-names
  FROM table-name1 
  JOIN table-name2 ON column-name1 = column-name2
  JOIN table-name3 ON column-name3 = column-name4
 WHERE condition

3-Table INNER JOIN syntax.

SELECT column-names
  FROM table-name1 
 INNER JOIN table-name2 ON column-name1 = column-name2
 INNER JOIN table-name3 ON column-name3 = column-name4
 WHERE condition

The INNER keyword is optional.
INNER JOINs are the most commonly used type of JOIN.


More Examples

JOIN with 3 TABLES

Problem: List all orders, sorted by order number, with product names, quantities, and prices.
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

SELECT O.OrderNumber, CONVERT(date,O.OrderDate) AS Date, 
       P.ProductName, I.Quantity, I.UnitPrice 
  FROM [Order] O 
  JOIN OrderItem I ON O.Id = I.OrderId 
  JOIN Product P ON P.Id = I.ProductId
 ORDER BY O.OrderNumber

This query performs 2 JOIN operations with 3 tables.
O, I, and P are table aliases. Date is a column alias.

Result:  2,155 records
OrderNumber Date ProductName Quantity UnitPrice
542378 7/4/2012 Queso Cabrales 12 14.00
542378 7/4/2012 Singaporean Hokkien Fried Mee 10 9.80
542378 7/4/2012 Mozzarella di Giovanni 5 34.80
542379 7/5/2012 Tofu 9 18.60
542379 7/5/2012 Manjimup Dried Apples 40 42.40
542380 7/8/2012 Jack's New England Clam Chowder 10 7.70
542380 7/8/2012 Manjimup Dried Apples 35 42.40
542380 7/8/2012 Louisiana Fiery Hot Pepper Sauce 15 16.80
542381 7/8/2012 Gustaf's Knäckebröd 6 16.80
542381 7/8/2012 Ravioli Angelo 15 15.60
542381 7/8/2012 Louisiana Fiery Hot Pepper Sauce 20 16.80
542382 7/9/2012 Sir Rodney's Marmalade 40 64.80
542382 7/9/2012 Geitost 25 2.00

You may also like



Last updated on Dec 21, 2023