CODEWITHSUNDEEP
sqlsql-server
Stefan Taseski
Stefan Taseski

Reputation: 232

Complex SQL Query with CROSS LEFT JOINS

Problem:

let's say i have 3 Common Table Expressions CTE1, CTE2, CTE3. They all contain MonthID, YearID, ProductID, StoreID, Amount

I have tables Years &Months and YearID &MonthID are their Primary Keys.

I need to create a report that contains all the data for given period so i do something like this:

SELECT MonthID, YearID, ProductID, StoreID, Amount
FROM Years 
CROSS JOIN Months
CROSS JOIN Products
INNER JOIN Store on Store.StoreId = Products.ProductId
LEFT JOIN CTE1 On CTE1.MonthID = Months.MonthID AND CTE1.YearID = Years AND (CTE1.ProductID = Products.ProductId OR CTE.StoreID = Store.StoreID)
LEFT JOIN CTE2 On CTE2.MonthID = Months.MonthID AND CTE2.YearID = Years AND CTE2.ProductID = Products.ProductId AND CTE2.StoreID = Store.StoreID
LEFT JOIN CTE3 On CTE3.MonthID = Months.MonthID AND CTE3.YearID = Years AND CTE3.ProductID = Products.ProductId AND CTE3.StoreID = Store.StoreID
WHERE CTE1.Amount IS NOT NULL OR CTE2.Amount IS NOT NULL OR CTE3 IS NOT NULL

The thing is in the CTE1 columns StoreID and ProductID can be NULL. Maybe ProductId, maybe StoreId, maybe both columns are NULL.

And i still need those values to be in my report .... so how do I do it?

Upvotes: 0

Views: 51

Answers (1)

xQbert
xQbert

Reputation: 35333

Consider using coalesce on the CTE1 value with the join criteria. This way when the CTE1.value is null; the value will be substitued for the value on the other side of the join. The net result is when the CTE1 value is null it basically evaluates to a 1=1 statement and no record would be eliminated.

Granted performance will be sub optimal as you're using a function which may negate index use.

SELECT MonthID, YearID, ProductID, StoreID, Amount
FROM Years 
CROSS JOIN Months
CROSS JOIN Products
INNER JOIN Store on Store.StoreId = Products.ProductId
LEFT JOIN CTE1 
  ON CTE1.MonthID = Months.MonthID 
 AND CTE1.YearID = Years 
 AND coalesce(CTE1.ProductID, Products.ProductId) = Products.ProductId 
 AND coalesce(CTE1.StoreID, Store.StoreID) = Store.StoreID
LEFT JOIN CTE2 
  On CTE2.MonthID = Months.MonthID 
 AND CTE2.YearID = Years 
 AND CTE2.ProductID = Products.ProductId 
 AND CTE2.StoreID = Store.StoreID
LEFT JOIN CTE3 
  On CTE3.MonthID = Months.MonthID 
 AND CTE3.YearID = Years 
 AND CTE3.ProductID = Products.ProductId 
 AND CTE3.StoreID = Store.StoreID
WHERE CTE1.Amount IS NOT NULL 
   OR CTE2.Amount IS NOT NULL 
   OR CTE3 IS NOT NULL

Upvotes: 1

Related Questions