Dave Dave - 3 months ago 15
SQL Question

T-SQL Complex Self Join

Scenario

I have a scenario which involves complex self-joins through another to return the correct result. What I am looking to do is create a Ms SQL Server (2008) View to present the data.

The situation involves individual companies that are within a chain holding stock of products and then having the option to share that stock with other companies within the chain to which they belong. The rules are as follows:


  • If a company holds stock of a product and they are sharing this stock then the quantity available to them is their stock plus any shared stock from companies within their chain

  • If a company holds stock of a product but they are not sharing this stock then the quantity available to them os their stock only

  • If a company does not hold stock then they only have the quantity available from other companies in their chain who are sharing the stock that they have



Tables

Create Table dbo.Chain(
ChainId int Identity(1,1) Not Null PRIMARY KEY,
Name nvarchar(255) Not Null)
Go
Insert into Chain(Name)Values('Technology Brothers')
Insert into Chain(Name)Values('Kings Of Tech')
Go

Create Table dbo.Company(
CompanyId int Identity(1,1) Not Null PRIMARY KEY,
Name nvarchar(255) Not Null,
ChainId int Not Null)
Go
Insert into Company(Name, ChainId)Values('Techies', 1)
Insert into Company(Name, ChainId)Values('MotherboardsRUs', 1)
Insert into Company(Name, ChainId)Values('RAM Bros', 1)
Insert into Company(Name, ChainId)Values('Silicon Station', 2)
Insert into Company(Name, ChainId)Values('Nerd Place', 2)
Go

Create Table dbo.Product(
ProductId int Identity(1,1) Not Null PRIMARY KEY,
Name nvarchar(255) Not Null)
Go
Insert into Product(Name)Values('Laser Mouse')
Insert into Product(Name)Values('MBoard 101')
Insert into Product(Name)Values('CAT Cable')
Insert into Product(Name)Values('Big Screen')
Insert into Product(Name)Values('J Speakers')
Insert into Product(Name)Values('Blue USB')

Create Table CompanyProduct(
CompanyProductId int Identity(1,1) Not Null PRIMARY KEY,
CompanyId int,
ProductId int,
Quantity int,
IsShared bit)
Go
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(1, 1, 10, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(1, 2, 1, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(1, 3, 5, 0)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(1, 4, 2, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(2, 1, 5, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(2, 2, 10, 0)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(3, 3, 1, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(3, 4, 4, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(3, 5, 10, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(4, 1, 5, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(4, 3, 10, 0)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(5, 2, 2, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(5, 4, 10, 1)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(5, 5, 3, 0)
Insert into CompanyProduct(CompanyId, ProductId, Quantity, IsShared)Values(5, 6, 8, 0)
Go


Expected Result

The results should include one row for each company for each product where there is quantity available to them. In other words, if they do not have any stock and no-one in their chain is sharing any then there will be no row for that product. Otherwise there will be a row with the quantity they own, the quantity shared with them and the total quantity they have available to them.

The below table outlines the results for the data inserted above.

CompanyId ChainId ProductId IsShared My Quantity Shared Quantity Total Quantity
1 1 1 1 10 5 15
1 1 2 1 1 0 1
1 1 3 0 5 NULL 5
1 1 4 1 2 4 6
1 1 5 NULL 0 10 10
2 1 1 1 10 5 15
2 1 2 0 10 NULL 10
2 1 3 NULL 0 1 1
2 1 4 NULL 0 6 6
2 1 5 NULL 0 10 10
3 1 1 NULL 0 15 15
3 1 2 NULL 0 1 1
3 1 3 1 1 0 1
3 1 4 1 4 2 6
3 1 5 1 10 0 10
4 2 1 1 5 0 5
4 2 2 1 0 2 2
4 2 3 0 10 NULL 10
4 2 4 NULL 0 10 10
5 2 1 NULL 0 5 5
5 2 2 1 2 0 2
5 2 4 1 10 0 10
5 2 5 0 3 NULL 3
5 2 6 0 8 NULL 8

Answer

It's a little messy, but I think you'd be able to use this to get the data you need.

SELECT com.name, pro.name, ISNULL(cop.Quantity, 0) [Own Stock], ISNULL(cop.Quantity, 0) + SUM(ISNULL(copsha.Quantity, 0)) [Shared Stock]
FROM Company com
JOIN Chain cha ON cha.ChainId = com.ChainId
LEFT JOIN Product pro ON pro.ProductId = pro.ProductId
LEFT JOIN CompanyProduct cop ON cop.CompanyId = com.CompanyId   
                    AND pro.ProductId = cop.ProductId
JOIN Company comsha ON comsha.CompanyId <> com.CompanyId
                AND comsha.ChainId = com.ChainId
LEFT JOIN CompanyProduct copsha ON (copsha.CompanyId = comsha.CompanyId
                            AND copsha.ProductId = pro.ProductId)
WHERE copsha.IsShared = 1
GROUP BY com.name, pro.name, cop.ProductId, cop.Quantity
HAVING ISNULL(cop.Quantity, 0) > 0
OR SUM(ISNULL(copsha.Quantity, 0)) > 0