asked on

Join Problems - Getting Cartesian Product (cross join) and Too Many Results

Hi,

I am trying to write a stored proc that returns data from several tables but I am getting a Cartesian product (cross join) and I am having problems getting rid of it. The problem occurs when I add both my Passengers table and my bags tables. For each transaction, there can be mulitple passengers, multiple bags and each bag can have multiple charges/descriptions (ex. a bag can be both over weight and over size). I don't care which bag belongs to which passenger as long as long as both passengers are not charged for both bags.

The relationships are:
tbTransactions
tbTransactions.TransactionID_tbBagHandling.TransactionID
tbTransactions.TransactionID_tbPassengers.TransactionID
tbPaymentTypes.PaymentTypeID_tbTransactions.PaymentTypeID

tbBagHandling
tbBagHandling.BagID_tbBagAttributes.BagID
tbTransactions.TransactionID_tbBagHandling.TransactionID

tbBagAttributes
tbAttributes.AttributeID_tbBagAttributes.AttributeID
tbBagHandling.BagID_tbBagAttributes.BagID

tbAttributes
tbAttributes.AttributeID_tbBagAttributes.AttributeID

As you can see from the results below, when I join in both my passenger table and my bags tables, I get a result for every combination of bag/passenger. I don't want to know which bag belongs to which passenger but I do need to make sure that each bag/attribute combination is only assigned to one passenger (seq).

Does anyone have an suggestions on how I can solve this? If I need to put in more info, just let me know.

AJ

The results I want to get are (I left out a lot of fields):
 
Transaction ID	PNR	Seq	Bag Tag	Charge Description
1	748L8Z	1	1234567890	Size (63 - 80 in)
1	748L8Z	2	9876543210	Weight (71-99 lbs)
 
 
The results I am getting:
Transaction ID	PNR	Seq	Bag Tag	Charge Description
1	748L8Z	1	1234567890	Size (63 - 80 in)
1	748L8Z	2	1234567890	Size (63 - 80 in)
1	748L8Z	1	9876543210	Weight (71-99 lbs)
1	748L8Z	2	9876543210	Weight (71-99 lbs)

Open in new window

Nathan Riley

What syntax are you using now? I assume your using all inner joins and no outer?

AJ0424

ASKER

I am sorry...I forgot to actually post the query! It is:

select 
	t.TransactionID as 'TransactionID',
	p.PNR,
	p.Sequence,
	bh.BagTag as 'BagTag',
	a.Description as 'Bag Charge Description',
	a.Charge as 'Line Amount',
	t.ChargeTotal as 'Charge Total',
	t.TipAmount as 'Tip Amount',
	'Transaction Total' = (t.ChargeTotal + t.TipAmount),
	pt.Description as 'Payment Type',
	'Card Info' = (t.CardLastFour + ' ' + t.CardCheckSum),
	t.AuthorizationCode as 'Authorization Code',
	t.RefundAuthorization as 'Refund Authorization',
	t.ChargeTimestamp as 'Charge Date',
	t.RefundTimestamp as 'Refund Date'
	
from tbTransactions t
inner join tbBagHandling bh on bh.TransactionID = t.TransactionID
inner join tbPaymentTypes pt on pt.PaymentTypeID = t.PaymentTypeID
inner join tbPassengers p on p.TransactionID = t.TransactionID
inner join tbBagAttributes ba on ba.BagID = bh.BagID
inner join tbAttributes a on a.AttributeID = ba.AttributeID

Open in new window

Nathan Riley

Give this a try:

select 
        t.TransactionID as 'TransactionID',
        p.PNR,
        p.Sequence,
        bh.BagTag as 'BagTag',
        a.Description as 'Bag Charge Description',
        a.Charge as 'Line Amount',
        t.ChargeTotal as 'Charge Total',
        t.TipAmount as 'Tip Amount',
        'Transaction Total' = (t.ChargeTotal + t.TipAmount),
        pt.Description as 'Payment Type',
        'Card Info' = (t.CardLastFour + ' ' + t.CardCheckSum),
        t.AuthorizationCode as 'Authorization Code',
        t.RefundAuthorization as 'Refund Authorization',
        t.ChargeTimestamp as 'Charge Date',
        t.RefundTimestamp as 'Refund Date'
        
from tbTransactions t
left outer join tbBagHandling bh on bh.TransactionID = t.TransactionID
left outer join tbPaymentTypes pt on pt.PaymentTypeID = t.PaymentTypeID
left outer join tbPassengers p on p.TransactionID = t.TransactionID
left outer join tbBagAttributes ba on ba.BagID = bh.BagID
left outer join tbAttributes a on a.AttributeID = ba.AttributeID

Open in new window

ASKER CERTIFIED SOLUTION

i2mental

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AJ0424

ASKER

i2mental,

That is what I was thinking but I am new enough that I was hoping there was another way around it that I was missing. I guess I will have to go down that route. Thanks.

Gallitin,
Unfortunately, that did not work but thank you for the help.

AJ