64970

SQL Cartesian product

Question:

<a href="https://i.stack.imgur.com/blG0p.png" rel="nofollow"><img alt="screenshot" class="b-lazy" data-src="https://i.stack.imgur.com/blG0p.png" data-original="https://i.stack.imgur.com/blG0p.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

The above results are of the second join operation below, FactPayTransaction and FactPairingSegments respectively.

I have two fact tables - FactPairingSegments and FactPayTransactions.

I am performing a join on these two as follows:

SELECT ps.Bidperiod, ps.PairingNumber, ps.ActBlockMin, ps.ActCreditMin, ps.ActDeadHeadMin, pt.EmployeeKey, pt.TransactionKey, pt.PayCreditMin, pt.Comment FROM FactPairingSegments ps, FactPayTransaction pt WHERE ps.Bidperiod = pt.BidPeriod AND ps.PairingDateKey = pt.PairingDateKey AND ps.PairingNumber = pt.PairingNumber AND pt.pairinglegnumber = ps.PairingLegNumber

However, this would retrieve only a subset of rows as there are a lot of rows in the PayTransaction table which have PairingLegNumber = NULL.

So I tried this with a union of previous query:

SELECT ps.Bidperiod, ps.PairingNumber, ps.pairinglegnumber, pt.PairingNumber, pt.PairingLegNumber, ps.ActBlockMin, ps.ActCreditMin, ps.ActDeadHeadMin, pt.EmployeeKey, pt.TransactionKey, pt.PayCreditMin, pt.Comment FROM FactPairingSegments ps, FactPayTransaction pt WHERE ps.Bidperiod = pt.BidPeriod AND ps.PairingDateKey = pt.PairingDateKey AND ps.PairingNumber = pt.PairingNumber AND pt.PairingLegNumber IS NULL

But this is causing a Cartesian product.

The problem I have is, the FactPairingSegments table does not contain the rows with NULL PairingLegNumber. These rows are only present in PayTransactions. Is there any way to avoid cross join?

Any help appreciated

Answer1:

I would try the following.

SELECT ps.Bidperiod ,ps.PairingNumber ,ps.ActBlockMin ,ps.ActCreditMin ,ps.ActDeadHeadMin ,pt.EmployeeKey ,pt.TransactionKey ,pt.PayCreditMin ,pt.Comment FROM FactPayTransaction pt LEFT JOIN FactPairingSegments ps ON ps.Bidperiod = pt.BidPeriod AND ps.PairingDateKey = pt.PairingDateKey AND ps.PairingNumber = pt.PairingNumber AND pt.pairinglegnumber = ps.PairingLegNumber;

Fact tables are typically weird like that cause they have like a gajilion primary key fields and you need to join on all of them. If one of your tables is missing data you might want to consider examining the data and make sure your keys/tables/tuples are set up correctly.

Answer2:

Thanks for the help, guys. The issue was that I was missing out on a pairing key which is why the results were weird.

Answer3:

I added the airline key to the join condition:

SELECT ps.Bidperiod ,ps.PairingNumber ,ps.ActBlockMin ,ps.ActCreditMin ,ps.ActDeadHeadMin ,pt.EmployeeKey ,pt.TransactionKey ,pt.PayCreditMin ,pt.Comment FROM FactPayTransaction pt LEFT JOIN FactPairingSegments ps ON ps.Bidperiod = pt.BidPeriod AND ps.PairingDateKey = pt.PairingDateKey AND ps.PairingNumber = pt.PairingNumber AND pt.pairinglegnumber = ps.PairingLegNumber AND pt.Airlinekey=ps.Airlinekey

Recommend