Selecting Columns from 3 Inner Joins - still getting cartesian product. S

Question

I have 3 tables. They are inner joined. When I choose to select from all three tables I get a cartesian product.

I've used distinct and I've tried cross apply with top 1. Top 1 brings back the right amount of records but it repeats the fields used in that select top N.

Basic question. Can you select from 3 different tables and avoid a cartesian result? I can have all three tables joined and with distinct I can get records from two of the tables without a cartesian. It is when I choose to select from a third is where the cartesian appears.

If this is possible, what other tsql commands/constructs should I be experimenting with? http://imageshack.us/f/255/50353790.png/

 SELECT CRT.[TransactionID]
      ,CRT.[creditrewardsID]
      ,CRT.[OwnerID]
      , CRT.[TransactionDate]
      ,CRT.[ItemID]
      ,CRT.[VALUE]
      ,CRM.First 
      ,CRM.MI 
      ,CRM.Last
      ,CTI.fn
      ,CTI.ln
  FROM [ownership].[dbo].[creditrewardsTransactions] CRT
  Join [ownership].[dbo].[creditrewardsMembers] CRM
    on CRT.creditrewardsid = CRM.[creditrewardsID]
  Join [Exchange].[dbo].[CreditTourInfo] CTI
    on CRM.CRMemberNum  = CTI.PRIMECRPNum
--where CRT.creditrewardsID = 11111

Can you **please** show us the actual **query** (the T-SQL code) that you're using? And yes - using proper JOIN's and proper join conditions, you can **definitely** select from three joined tables **without** a Cartesian product... — marc_s, Nov 15 '12 at 13:36
Can you also provide some sample data and outputs? If you're using the above query, then you are not getting a cartesian, you are getting the expected results (assuming your join conditions are correct). To help any more, we'll need to see the data.. — StevieG, Nov 15 '12 at 15:02
For each record in CreditTourInfo I get 64 records back. So if CreditTourInfo has 10 records I get 10*64. — user1826685, Nov 15 '12 at 15:22
I don't get a cartesian product when I don't select from the CTI table. I can use it in the join and when I don't select any columns from it I don't get too many records back. — user1826685, Nov 15 '12 at 15:24
Basically a particular individual has a creditrewardsid in CRT and CRM. That same person has a CRMemberNum in CRM and a PRIMECRPNum in CTI. That is where the joins are happening. When I use the where clause to work on a particular indivual... I can see that person has 59 records in the CTI table and 64 records in the CRT table. So I get 3776 records back. I am trying to group but I either get an error and if I don't get an error I get the same amount of records back. I think I need some other clause to restrict. — user1826685, Nov 15 '12 at 15:31
As I think about it - I think I need to work someone with each transaction in the CRT table. Have them relate back to each associated record in the CTI table somehow. — user1826685, Nov 15 '12 at 15:39
That is the way it works. You need, for example, to decide which of the 64 records in the CRT table you want to see. If you only want one record to appear you need to pick only one. For example in your select, you select the member id AND the transaction id. To see that, you need to see all 64 records! If instead you picked the MAX(transactionid), you would only see the largest transaction id. So have a think about what you actually want to see... the sum of transactions? — Nick.Mc, Nov 19 '12 at 13:32

score 0 · Answer 1 · answered Mar 15 '13 at 23:19

To answer your "basic" question, yes you can easily join 3 tables and not receive a Cartesian product. However, you need to ensure you have proper One-to-One or One-to-Many joins.

The issue above likely has to do with the data you have in the creditrewardsMembers and CreditTourInfo tables. Joining the CRMemberNum and PRIMECRPNum fields is likely a Many-to-Many join, which will give you a Cartesian product.

You can run these SQL statements to confirm. If they both return records, then you have a Many-to-Many join issue.

SELECT CRM.CRMemberNum, COUNT(*)
  FROM [ownership].[dbo].[creditrewardsMembers] CRM
 GROUP BY CRM.CRMemberNum
HAVING COUNT(*) > 1

SELECT CTI.PRIMECRPNum, COUNT(*)
  FROM [Exchange].[dbo].[CreditTourInfo] CTI
 GROUP BY CTI.PRIMECRPNum
HAVING COUNT(*) > 1

If the fields are in fact the correct ones to join, then you may need to just pick the first record from one of the tables in your query like this.

SELECT CRT.[TransactionID]
      ,CRT.[creditrewardsID]
      ,CRT.[OwnerID]
      ,CRT.[TransactionDate]
      ,CRT.[ItemID]
      ,CRT.[VALUE]
      ,CRM.First 
      ,CRM.MI 
      ,CRM.Last
      ,CTI.fn
      ,CTI.ln
  FROM [ownership].[dbo].[creditrewardsTransactions] CRT
  JOIN [ownership].[dbo].[creditrewardsMembers] CRM
    ON CRT.creditrewardsid = CRM.[creditrewardsID]
  JOIN (
        SELECT PRIMECRPNum, MIN(fn) as [fn], MIN(ln) as [ln]
          FROM [Exchange].[dbo].[CreditTourInfo]
         GROUP BY PRIMECRPNum
       ) CTI
    ON CRM.CRMemberNum  = CTI.PRIMECRPNum

Ultimately, it's going to depend on the limitations of your specific data model and the tradeoffs you may need to make. (ie. getting the first record only from [CreditTourInfo])

Selecting Columns from 3 Inner Joins - still getting cartesian product. S

1 Answers1