In a real world scenario, we can sale in our online store on standard or discounted prices.
In this exercise we use three tables to demonstrate the zig-zag JOIN:
Create database
USE [master]; GO IF (DB_ID('PL') IS NOT NULL) BEGIN ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE PL; END GO CREATE DATABASE PL; GO USE PL; GO
Create test tables
Sales
Data (or Fact) table that stores information for customer, item and quantities. Column IsItemDiscounted is the tricky one. It is used to determine how we route the link between Sales and Items tables.
IF (OBJECT_ID('PL.dbo.Sales', 'U') IS NOT NULL) BEGIN DROP TABLE PL.dbo.Sales; END GO CREATE TABLE PL.dbo.Sales ( SaleID INT IDENTITY(1, 1) PRIMARY KEY , CustomerID INT , ItemID INT , IsItemDiscounted BIT , Quantity INT ); GO INSERT PL.dbo.Sales ( CustomerID , ItemID , IsItemDiscounted , Quantity ) SELECT 1, 11, 1, 52 UNION ALL SELECT 2, 22, 1, 2 UNION ALL SELECT 3, 11, 1, 62 UNION ALL SELECT 1, 22, 0, 552 UNION ALL SELECT 1, 11, 0, 1; GO SELECT * FROM PL.dbo.Sales; GO
ItemsStandardDiscounted
Links ItemID (the standard item – regular price and item attributes) to DiscountedItemID (discounted item – different price and item attributes)
IF (OBJECT_ID('PL.dbo.ItemsStandardDiscounted', 'U') IS NOT NULL) BEGIN DROP TABLE PL.dbo.ItemsStandardDiscounted; END GO CREATE TABLE ItemsStandardDiscounted ( ItemID INT , DiscountedItemID INT ); GO INSERT PL.dbo.ItemsStandardDiscounted ( ItemID , DiscountedItemID ) SELECT 11, 111 UNION ALL SELECT 22, 222; GO SELECT * FROM PL.dbo.ItemsStandardDiscounted; GO
Items
In this table we store the standard and discounted items on separate rows. This gives us the chance to treat them separately.
IF (OBJECT_ID('PL.dbo.Items', 'U') IS NOT NULL) BEGIN DROP TABLE PL.dbo.Items; END GO CREATE TABLE PL.dbo.Items ( ItemID INT , Price MONEY ); GO INSERT PL.dbo.Items ( ItemID , Price ) SELECT 11, 5.23 UNION ALL SELECT 22, 15.88 UNION ALL SELECT 111, 4.99 UNION ALL SELECT 222, 14.26; GO SELECT * FROM PL.dbo.Items; GO
The DML Query Statement that is using zig-zag JOINing:
SELECT S.CustomerID , S.ItemID , S.IsItemDiscounted , S.Quantity , ISD.ItemID , ISD.DiscountedItemID , I.ItemID , I.Price , (S.Quantity * I.Price) AS SavesValue FROM PL.dbo.Sales AS S JOIN PL.dbo.ItemsStandardDiscounted AS ISD ON S.ItemID = ISD.ItemID JOIN PL.dbo.Items AS I ON IIF((S.IsItemDiscounted = 0), ISD.ItemID, ISD.DiscountedItemID) = I.ItemID; GO
The logic of this statement:
- Column IsItemDiscounted in Sales table shows what type of item is sold – standard or discounted
- Table ItemsStandardDiscounted routes (uses as a link column to table Items) to the standard or discounted item
- Table Items is linked to table ItemsStandardDiscounted, based on IsItemDiscounted in Sales table
The result:
Translation:
- On the first row ItemID = 11 is linked to ItemID = 11. Hense IsItemDiscounted is 1, DiscountedItemID = 111 is linked to ItemID = 111 and the price of ItemID = 111 (4.99) is used in the calculation (52 * 4.99 = 259.48)
- On the last row ItemID = 11 is linked to ItemID = 11. Hense IsItemDiscounted is 0, ItemID = 11 is linked to ItemID = 11 and the price of ItemID = 11 (5.23) is used in the calculation (1 * 5.23 = 5.23)
Are you new in SQL Server?
Do you struggle with your SQL understanding? I can’t blame you. The SQL books these days are so boring and hard to follow.
My brand new book Learn SQL Server Intuitively. Transact-SQL: The Solid Basics changes everything.
Keep it simple :-)