Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    55

    Unanswered: Cartesian Product

    Hello,

    These 2 queries run fine seperately. But when I try to join the two I get something that's alomost close to a cartesian product. I can not figure out why. Can anyone point me in the right direction?

    Thanks for your help!


    QUERY 1:
    select entity_id
    from biotab1
    where primary_record_type = 'pa'
    and (spouse_primary_record_type <> 'al'
    or spouse_primary_record_type is null)


    QUERY 2:
    select entity_id
    from giftab1
    where yrgiving = '2009'
    and trantype in ('gi', 'pp', 'mc')


    COMBINED QUERY:
    select a.entity_id, b.entity_id
    from biotab1 a
    inner join giftab1 b on a.entity_id = b.entity_id
    and a.primary_record_type = 'pa'
    and (a.spouse_primary_record_type <> 'al'
    or a.spouse_primary_record_type is null)
    and b.yrgiving = '2009'
    and b.trantype in ('gi', 'pp', 'mc')

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    The most obvious thing that jumps out at me is that in your join you have combined the join constraints and the WHERE clause constraints.

    Without putting much thought into it beyond a casual look through your post, you need to do something like
    Code:
    select a.entity_id, b.entity_id
    from biotab1 a
    inner join giftab1 b on a.entity_id = b.entity_id
    WHERE a.primary_record_type = 'pa' 
    and (a.spouse_primary_record_type <> 'al'
    or a.spouse_primary_record_type is null)
    and b.yrgiving = '2009'
    and b.trantype in ('gi', 'pp', 'mc')
    Try that first and see where it gets you.

    Also, it would help if you could provide test data and table defs in your post. It would help make it easier to do more than just "casually browse through your post"

    as a side note.....it would also help to "pretty up" your code a bit by indenting and all that fun stuff.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    BTW...why are you selecting the a.entity_id and b.entity_id columns from your joined table when they are the actual join/key columns? They will both be the same in any rows returned by definition of the join.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what about this...
    Code:
    SELECT entity_id
      FROM biotab1
     WHERE primary_record_type = 'pa' 
       AND (
           spouse_primary_record_type <> 'al'
        OR spouse_primary_record_type IS NULL
           ) 
    UNION 
    SELECT entity_id
      FROM giftab1
     WHERE yrgiving = '2009'
       AND trantype IN ('gi', 'pp', 'mc')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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