15,920,508 members
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.

[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
Updated 18-Feb-11 7:44am
v4
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.

## 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.

## Solution 2

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.

v3
Henry Minute 18-Feb-11 13:44pm
The OP has added more detail as an answer. I have moved it into the question.

## 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
)```

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. :)

## Solution 5

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!

[no name] 18-Dec-12 8:13am
...
000
1100
2200