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

Sales

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

ItemsStandardDiscounted

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

Items

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:

1. Column IsItemDiscounted in Sales table shows what type of item is sold – standard or discounted
2. Table ItemsStandardDiscounted routes (uses as a link column to table Items) to the standard or discounted item
3. 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 :-)