duplicate rows occuring when using inner join

Question

0.00/5 (No votes)

See more:

Hi all I have these two tables with column names as

table1-comm,country
table2-fee,country

I need to display these column comm,fees using the country, but my problem here is I used a innerjoin I am getting duplicate rows as I was going wrong somewhere in writing join condition.

The problem here is the table 1 has 32 countries and table 2 has 60 countries what I want to do is have to get the fees values from table2 with respect to table 1 countries so I have to get 32 rows as a result out of 60 since my table 1 has 32 countries I used the following script:

select a.comm,b.fee from table1 a inner join table2 b
  on a.country=b.country
  where b.country in (select distinct country from table1)

Is that script right?

Please correct me where am I doing mistake.

Thanks in advance

[Edit - Moved here from answer by OP - Henry]
moreover the table lookslike this
table a comm, country table b fees country
1.6 usa 3.2 usa
1.3 uk 4.9 uk
1.8 uk 3.2 ger
1.9 uk 4.9 ind
11.69 usa 5.6 aus
so what I need from this is sum(all comm) from table a and sum(fees) for country which are common in table A
so result should be (1.6+1.3+1.8+1.9+11.69)=30.4
and (3.2+4.9)=13
final result should be 30.4,13
[/Edit]

Posted 18-Feb-11 5:12am

makwith9789

Updated 18-Feb-11 7:44am

Henry Minute

v4

Add a Solution

Comments

Chris Meech 18-Feb-11 15:52pm

Perhaps it is immaterial, but how does (1.6+1.3+1.8+1.9+11.69)=30.4 ?? and (3.2+4.9)=13 ?? The first sum should result in 18.29 and the second in 8.1? Seems odd.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Henry Minute · Answer 1 · 2011-02-18T05:25:00

Solution 1

To get just the 32 rows of table 1 you could use LEFT OUTER JOIN instead of your INNER JOIN.

You should be aware, though, that it will return all rows from table1, even if there is no match in table2.

Posted 18-Feb-11 5:25am

Henry Minute

Dylan Morley · Answer 2 · 2011-02-18T05:44:00

It sounds like you've got multiple records in your table2 for a particular country

E.g.

data in table1

comm       country
fff        UK

data in table 2

fee        country
15         UK
25         UK
50         UK

If you join against this data, you will see multiple rows since you haven't told it to perform any aggregation. When inner joining table1 against table2 as above, returning 3 rows is correct - it's satisfied your criteria

In addition to this, what 'fee' record should it display? There are 3 in the above, there's no way of uniquely retrieving the data.

What you might want to do is find the SUM of the values for a particular country, then join on that.

E.g.

SQL

SELECT
    a.comm, ISNULL(SumOfFees.Fees, 0) AS Fees
FROM
    table1 A
LEFT JOIN
    (
        SELECT
            country, SUM(fee) AS Fees
        FROM
            table2
        GROUP BY
            country
    )
    AS
        SumOfFees
ON
    A.country = SumOfFees.country

So, the sub-query performs a GROUP BY and SUM of the fee field, which will return unique records at country level.

We now perform a LEFT JOIN against the country code field, so this will return all of the data from table1 and the sum of fees from our sub query.

Chris Meech · Answer 3 · 2011-02-18T08:47:00

Solution 4

Try this

select * from (
select country
      ,sum(comm) comm_sum
      ,0 fee_sum
  from table1
 group by country
union all
select country
      ,0 comm_sum
      ,sum(fee) fee_sum
  from table2
 where exists ( select a.country from table1 a where a.country = table2.country )
 group by country
)

Posted 18-Feb-11 8:47am

Chris Meech

Comments

makwith9789 18-Feb-11 15:14pm

IS THERE ANY WAY WE CAN DO IT WITH OUT USING UNION JUST BY USING JOINS

Chris Meech 18-Feb-11 15:53pm

Maybe. But I'd have to think about it a whole lot longer. :)

Costica U · Answer 4 · 2011-02-18T09:55:00

You have duplicate rows because there are duplicate in table1 or table 2
The scrpit should be:

select a.comm,
       b.fee
from   table1 a 
inner
join   table2 b
       on a.country=b.country

Note that the where condition is not needed.

To check for duplicate run the script:

select country,
       count(*)
from   table1
group
by     country
having count(*)>1

select country,
       count(*)
from   table2
group
by     country
having count(*)>1

Hope this help!

duplicate rows occuring when using inner join

4 solutions

Solution 1

Solution 2

Solution 4

Solution 5

Add your solution here

Preview 0