Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: Left Join on 2 columns returning dupes

    Hey Guys,

    I am trying to join 2 tables together on 2 columns but I am getting duplicates which is increasing my sales total... please help!

    TABLEA - has the city, state and sales data
    TABLEB - has the DMA region codes (breaks down the state into regions)


    select
    a.city,
    a.state,
    b.dma,
    sum(sales)
    from tablea as a
    left outer join tableb b on (a.state = b.state and a.city = b.city)
    group by
    a.city,
    a.state,
    b.dma

    I am looking to pull in the DMA code so I can further breakdown sales by region.

    I have created a simplified version of the query for dbforums

    thanks!
    - Using Oracle 11g with Toad v12

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Why left outer join?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2011
    Posts
    34
    It is not required for customers to enter a city or state.

    I will handle nulls as unknown and a DMA code of 999

    Hope this helps
    - Using Oracle 11g with Toad v12

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you have any duplicate rows with the same city and state in your DMA table?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT DISTINCT a.city, 
                    a.state, 
                    b.dma, 
                    SUM(sales) 
    FROM   tablea AS a 
           left outer join tableb b 
                        ON ( a.state = b.state 
                             AND a.city = b.city ) 
    GROUP  BY a.city, 
              a.state, 
              b.dma
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2011
    Posts
    34
    There are duplicate rows in the DMA table.

    The code above is the same as mine.
    - Using Oracle 11g with Toad v12

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If there are duplicate rows (same city and state) in the DMA table, then by definition there should be duplicate rows in the result set. Nothing in Oracle is broken, you are getting the results that your data demands.

    If you don't want the duplicates in your output, you need to decide how to pick only one of the duplicate DMA rows and add criteria to either the JOIN or the WHERE clause to exclude the unwanted rows from the result set. If you need both DMA codes reported, then you must accept the duplicated values.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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