Problem with Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with Join

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-24 : 08:19:51

I have a join that i like to peform, but my problem is that i keep getting duplicate records.

what i have is a parts and a Rates table

In my parts table one column name is currency that holds currency name like EUR , GPB

In my Rates table i have a currnecy colum and a rates column they hold currency like Eur, GPB and value of this currency 1.27 and . 65

I want to do a join on the Parts table with the Rates table and then use the rate field to calculate the currency into $,

but i keep getting duplicate rows, my Rate table only hold 14 rows as i've got 14 different currencies i've created a unique id in each table and that did not helo me either.

Here is my code, its in Access..

SELECT ZCONS_XSAP_parts.ID, PDR_Rate.Rate, ([ZCONS_XSAP_parts].[TotMoving price]/[PDR_Rate].[Rate]) AS [$ Cost]
FROM ZCONS_XSAP_partss LEFT JOIN PDR_Rate ON ZCONS_XSAP_parts.Curr = PDR_Rate.Currency;

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 08:32:56

why are you using Left outer join, use Inner Join

or

Post the 14 records from both of the table.

Chirag

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-24 : 08:38:35

What happens when you leave out the followeing part:
', ([ZCONS_XSAP_parts].[TotMoving price]/[PDR_Rate].[Rate]) AS [$ Cost]'

Do you still get duplicate records?
And as Chirag says; post the records and why the left join?

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 09:22:40

"why are you using Left outer join, use Inner Join "

You are assuming that there is guaranteed to be a rate record, and ZCONS_XSAP_parts.Curr is NOT NULL - which we don't know!

rookie_sql:

Have you got duplicate records in PDR_Rate ??

Does:

SELECT Currency, COUNT(*)
FROM PDR_Rate
GROUP BY Currency
HAVING COUNT(*) > 1

list any (duplicates)?

Kristen

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-24 : 09:32:36

Sorry, here is the correct Join, i was messing around with the link and had the wrong option selected.

SELECT ZCONS_XSAP_Orders.ID, PDR_Rate.Rate, ([ZCONS_XSAP_Orders].[TotMoving price]/[PDR_Rate].[Rate]) AS [$ Cost]
FROM ZCONS_XSAP_Orders INNER JOIN PDR_Rate ON ZCONS_XSAP_Orders.Curr = PDR_Rate.Currency;

In the Rate Table yes there is duplicate currencies, and i have been given a updated table now, which contains the country, the country column is unique 14 different countires, so if i update the parts table based on the city = to a country i can do a join on that ?

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-24 : 09:37:52

Here is my rate table.

Country Currency Rate
FRANCE n/a 0
GERMANY EUR 0.82
ITALY EUR 0.82
SPAIN EUR 0.82
UK GBP 0.55
AUSTRIA EUR 0.82
DENMARK DKK 6.11
FINLAND EUR 0.82
HOLLAND EUR 0.82
IRELAND EUR 0.82
NORWAY NOK 6.29
PORTUGALEUR 0.82
SWEDEN SEK 7.51
SWITZERLAND CHF 1.25

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 09:40:06

Can I rephrase that back to you and see if you agree?

You now have a new RATE table with 14 ROWS, and the Country column in that table is unique?

Or do you mean that RATE table has more than 14 rows, but there are only 14 different Country values shared between the rows?

"so if i update the parts table based on the city = to a country i can do a join on that"

Now I'm lost!

Your example has ORDERS and RATEs table (pedantic point: you should be consistent in your naming - either they are singular, or plural, but not a mixture!).

Now you mention a PARTS table and joining a CITY to a COUNTRY. You'll need to explain that some more please ...

Kristen

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-24 : 09:50:41

ok, sorry,

2 tables Parts , Rates,

Rates has, country, Currency, Rate
only 14 rows in my table 14 different countries, but the currecny is duplicate in some cases as the Euro is used in many different countries, so the rate will be the same,

Parts table has a currency name column, eg. Eur. and a cost column I want to calculate what the cost of the part is into dollors, i need to join my rates table with my parts table.

I actually go it to work as when you mentioned was my rates table holding duplicate records for the currencies i said yes i created a query to only display unique currency and then did the inner join on parts and rate with the currency.

Here is my new query

SELECT ZCONS_XSAP_parts.*, ([ZCONS_XSAP_parts].[TotMoving price]/[PDR_Rate].[Rate]) AS [$ Cost]
FROM Unique_Currency_Rate INNER JOIN ZCONS_XSAP_parts ON Unique_Currency_Rate.Currency = ZCONS_XSAP_Orders.Curr;

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-24 : 09:50:54

Rookie, the query you created creates duplicate rows. Each ID which has the currency 'EUR', will join with each country where currency equals 'EUR'. You could use the function DISTINCT, but this doesn't improve performance (if interesting here!). Other option is creating another table which contains 2 columns; city and country

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-24 : 09:52:47

Okay, just read your reply. Also a possibility.

By the way:
France, EUR, 0.82 ;)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 09:57:07

Just Try these works in Ms Acess and gives you what you want?


SELECT ZCONS_XSAP_Orders.ID, PDR_Rate.Rate, ([ZCONS_XSAP_Orders].[TotMoving price]/[PDR_Rate].[Rate]) AS [$ Cost]
FROM ZCONS_XSAP_Orders INNER JOIN 
(Select Distinct Currency,Rate From PDR_Rate ) As PDR_Rate
ON ZCONS_XSAP_Orders.Curr = PDR_Rate.Currency

Chirag

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-24 : 10:05:35

Hi chirag,

Thats sub distinct select worked also, sorry about the confusion i had 2 queries one named orders and the other parts. Main thing it works now and i learnt more about joins, and that was my aim, thanks for all your help guys..

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 10:47:41

rookie_sql you should normalise your table:

Country should have a Currency code

and then

a new Currency table should have a unique Currency code and the exchange rate.

Otherwise what's to stop each of the EUR exchange rates from being changed differently? And if they were different how would you know which one to believe?

Or is the Irish Euro worth more than the French one?!

"Parts table has a currency name column, eg. Eur."

Alternatively you could change that to store COUNTRY rather than CURRENCY code (of course with a CURRENCY table as I describe you could JOIN to that directly)

"i created a query to only display unique currency"

Consider what would happen IF the rates for EUR became different between the EC countries - which they might well do DURING an update where an operating is working through the countries changing them one-by-one ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources