Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    5

    Unanswered: Simple Join not so simple

    I am receiving funny results from a query. To simplify, I have 2 tables (today\yesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.

    result set

    colA colB colC colD colE colF colG colG (from yesterday)
    1 1 a b c d e m
    1 1 a b c d m e

    So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.

    Any help is appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We can't help fix your query if you don't post your query.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2007
    Posts
    5
    select t.colA,
    t.colB,
    t.colC,
    y.colC,
    t.colD,
    y.colD,
    t.colE,
    y.colE,
    t.colF,
    y.colF,
    t.colG
    y.colG
    from today t inner join yesterday y
    on t.colA = y.colA
    and t.colB = y.colB
    where ((t.colF <> y.colF) or (t.colG <> y.colG))

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the examples you show are fine, those aren't the same rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do you have primary keys on these tables? Is there a natural key for each table that uniquely identifies a record?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2007
    Posts
    5
    There aren't defined keys, but colA and colB combined make the key. The key is not unique though as there could be multiple records. To make the key unique it would have to include both colF and colG but then these are subject to change (which is what I am checking).

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it is not unique, it is not a key. And if you are joining on this and there are duplicates, then you will get duplicate records out. If you have two records in A that match with 2 records in B, you are going to get 4 records as a result. There is no way around that.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Since 4 columns make it unique it might help to join where all 4 are equal

    select *
    from today t
    where not exists
    (select 1
    from yesterday y
    where t.colA = y.colA
    and t.colB = y.colB
    and t.colF = y.colF
    and t.colG = y.colG
    )

Posting Permissions

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