Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2012
    Posts
    11

    Unanswered: Selecting Columns from 3 Inner Joins - still getting cartesian product

    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/

  2. #2
    Join Date
    Jun 2012
    Posts
    11
    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

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    So, what exactly is the code that is giving you a cartesian product?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The query appears to be formed correctly, do you have a lot of duplicates in these columns?
    creditrewardstransactions.creditrewardsid
    creditrewardsMembers.creditrewardsid
    creditrewardsMembers.CRMemberNum
    CreditTourInfo.PRIMECRPNum

  5. #5
    Join Date
    Jun 2012
    Posts
    11
    Quote Originally Posted by MCrowley View Post
    So, what exactly is the code that is giving you a cartesian product?
    The code I posted it producing the cartesian product.

    For a particular creditrewardsid I have 59 records in the CTI table and 64 records in the CRT table. 1 record in the CRM table that links between the other two.

  6. #6
    Join Date
    Jun 2012
    Posts
    11
    Quote Originally Posted by MCrowley View Post
    The query appears to be formed correctly, do you have a lot of duplicates in these columns?
    creditrewardstransactions.creditrewardsid
    creditrewardsMembers.creditrewardsid
    creditrewardsMembers.CRMemberNum
    CreditTourInfo.PRIMECRPNum
    Yes for each record in CreditTourInfo I get 64 records back. So if CreditTourInfo has 10 records I get 10*64.

    I tried using group by but I either get an error and if I don't I still get the same amount of records.

  7. #7
    Join Date
    Jun 2012
    Posts
    11
    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.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    OK, so grouping by all the columns does not reduce the number of rows returned, because all of the rows returned are unique. What is the output you are after?

  9. #9
    Join Date
    Jun 2012
    Posts
    11
    Quote Originally Posted by MCrowley View Post
    OK, so grouping by all the columns does not reduce the number of rows returned, because all of the rows returned are unique. What is the output you are after?
    Per each transaction in the CRT table I want other relative info from the other two tables.

    After further research it looks like there maybe another table in another database that should help me get what I'm looking for. I don't think I have enough here in these tables to narrow the results.

  10. #10
    Join Date
    Jun 2012
    Posts
    11
    Quote Originally Posted by MCrowley View Post
    OK, so grouping by all the columns does not reduce the number of rows returned, because all of the rows returned are unique. What is the output you are after?
    You've been a great help. You allowed me to accept what was returned was correct. I just need to bring more into the equation to get what I'm looking for.

    Thanks a million.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •