SQL Primer: Using Left Join . . . On


This example shows how LEFT JOIN is used to determine the total number of orders for each ProductCode listed in the Products3 table.

The Products3 table lists each ProductCode, while the quantities ordered for each ProductCode are listed in the Order_Details2 table. LEFT JOIN links the Products3 (left) and Order_Details2 (right) tables ON ProductID as shown in the SQL source below.


ProductType ProductCode SumOfQuantity ProductName UnitPrice
Backpack AW140-13 University $ 140
Backpack AW151-13 Pacific $ 151
Backpack AW175-13 Conestoga $ 175
Backpack AW325-13 Aces $ 325
Boot AW087-04 10 Sierras $ 87
Boot AW098-04 9 Everglades $ 98
Boot AW078-04 1 Rockies $ 78
Boot AW074-04 Dunes $ 98
Carabiner AW007-08 Gold-Series Locking D $ 7
Carabiner AW009-08 Black Diamond Quicksilver II $ 9
Carabiner AW013-08 Petzl Spirit $ 13
Carabiner AW014-08 Basic Black $ 14
Crampon AW099-15 Venus $ 99
Crampon AW109-15 Wafflestomper $ 109
Crampon AW145-15 Edgehugger $ 145
Crampon AW149-15 Glory Grip $ 149
Harness AW029-10 Black Diamond Alpine Bod $ 29
Harness AW032-10 Black Diamond Bod $ 32
Harness AW053-10 El Capitan $ 52
Harness AW072-10 Petzl Mercury $ 72
Pants AW032-01 Women's 4-pocket pants $ 32
Pants AW046-01 Men's 8-pocket conversion pants $ 46
Pants AW048-01 Unisex drawstring pants $ 48
Pants AW055-01 Unisex hiking pants $ 55
Parka AW114-06 Sahara $ 114
Parka AW125-06 Crystal $ 125
Parka AW188-06 Alpine $ 188
Parka AW250-06 Campos $ 250
RockShoes AW084-05 Morro $ 84
RockShoes AW086-06 Tuscany $ 124
RockShoes AW125-05 Plymouth $ 125
RockShoes AW150-05 Gibralter $ 150
Shirt AW029-03 Women's woven tee $ 29
Shirt AW042-03 Men's button-down $ 42
Shirt AW045-03 Men's loose weave polo $ 45
Shirt AW075-03 Unisex long-sleeve button-down $ 75
SleepingBag AW310-12 8 Polar Star $ 310
SleepingBag AW200-12 4 Big Sur $ 200
SleepingBag AW390-12 4 North Face Sunspot $ 390
SleepingBag AW210-12 Cascade $ 210
Supplies AW031-14 Daytrip First Aid Kit $ 31
Supplies AW051-14 Wolfgang $ 59
Supplies AW425-14 Surelite $ 4
Supplies AW475-14 Scoutpride $ 44.75
Tent AW190-11 Scirocco $ 190
Tent AW335-11 Aptos $ 335
Tent AW425-11 Starlight $ 425
Tent AW535-11 Galaxy $ 535

The SQL Source of the Recordset is as follows:

SELECT DISTINCT ProductType, ProductCode, Sum(Order_Details2.Quantity) AS SumOfQuantity, ProductName, Products3.UnitPrice FROM Products3 LEFT JOIN Order_Details2 ON Products3.ProductID = Order_Details2.ProductID GROUP BY ProductType, ProductCode, ProductName, Products3.UnitPrice ORDER BY ProductType, Sum(Order_Details2.Quantity) DESC, ProductCode

This Recordset includes fields from two tables. When selecting fields from multiple tables, the fields must be referenced as tableName.fieldName if the same field appears in more than one table.

Example: Order_Details2.ProductID, Products3.ProductID

This is required to explicitly specify from which table the values of conflicting fields are retrieved, and returns an error if not defined.

Go Top   |   Return to DB101 ADO Samples