Click here to Skip to main content
14,739,836 members
Please Sign up or sign in to vote.
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
Updated 18-Feb-11 8:44am
v4
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.

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

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
Comments
Henry Minute 18-Feb-11 13:44pm
   
The OP has added more detail as an answer. I have moved it into the question.
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
)
   
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. :)
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!
   
Comments
[no name] 18-Dec-12 8:13am
   
...
000
1100
2200

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900