Problem with SQL Statement - DISTINCT / JOIN or what?

[OnlineD]

I have just about reached the limits of my knowledge with a SQL statement that I am trying to write that pulls in data from multiple tables.

Basically I have a situation where I have a Fund in one currency and a Share class in another currency. Investments are made in the share class currency and on the page I need to show the amount invested in the share class (SCurrencyID) currency and the equivalent taking into account the FX in the Fund (BCurrencyID) currency. The calculations to do this are easy enough and are done on the fly as the page builds, providing of course the data set pulled in is correct.

Therefore I am trying to pull both currencies into the SQL statement, partly because I also need to show both corresponding currency symbols on the page (held in the Currencies table). However what is happening is I am getting duplicated entries in the output. I am thinking this is because of the 2 currency references in the SQL.

I have a feeling I need to use perhaps Distinct or Join in some way in the SQL but I am not at all clear how this will help, or how to implement it.

Can anyone help?

Here is my current SQL statement that returns the duplicate results.

SELECT *
FROM Investments I, Funds F, Funds_Shareclasses C, Currencies R, Companies P
WHERE I.FundID = F.FundID
AND F.BCurrencyID = R.CurrencyID   
AND C.SCurrencyID = R.CurrencyID
AND I.CompanyID = P.CompanyID   
AND I.CompanyID LIKE varCompanyID  
AND I.FundID LIKE varFundID
ORDER BY I.Date DESC

[hometownnerd]

You probably want to use JOINS in this case, what is happening is it is matching all entries to all rows which dumps huge amount of rows. Try left joins so that you match only those that actually match.

SELECT *
FROM Investments I left join Funds F on I.FundID = F.FundID left join Currencies R on F.BCurrencyID = R.CurrencyID left join Funds_Shareclasses C on C.SCurrencyID = R.CurrencyID left join Companies P on I.CompanyID = P.CompanyID 
WHERE I.CompanyID LIKE varCompanyID  
AND I.FundID LIKE varFundID
ORDER BY I.Date DESC

I am not going to say it will work 100% since I do not have your DB layout here but it may be close enough to give you some help.

Let me know,
Larry

[cyberdaemon]

you can use join in your queries.

SELECT * FROM Investments I inner join Funds F on I.FundID = F.FundID inner join Currencies R on F.BCurrencyID = R.CurrencyID inner join Funds_Shareclasses C on C.SCurrencyID = R.CurrencyID inner join Companies P on I.CompanyID = P.CompanyID WHERE I.CompanyID LIKE varCompanyID AND I.FundID LIKE varFundID5.ORDER BY I.Date DESC