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