sql question

  • Thread starter Thread starter Nikolay Petrov
  • Start date Start date
N

Nikolay Petrov

I have three really small tables, which gave me a lot of headache.
They reside in a MS Access database and a I am accessing them through
ADO.NET(VB).
Can't figure out how to make a "Select" query on them.
These are the tables:
1St - "Transactions"
TransactionID - primary key, autogen
TransactionNum - alphanumeric
TransactionDate - short date
Amount - number
CurrencyID - number - foreign key to Currencies
ForeginCurrencyAmount - number
CurrencyID - number - foreign key to Currencies
SenderName - text
SenderCountryID - number - foreign key to Countries
ReceiverName - text
ReceiverCountryID - number - foreign key to Countries
2nd table - "Currencies":
CurrencyID - primary key, autogen
CurrencyName - text
3rd table - "Countries":
CountryID - primary key, autogen
CountryName - Text

I need a query which shows the following:
TransactionDate
Amount
CurrencyName(for Amount)
ForeginCurrencyAmount
CurrencyName (for Foregin Amount)
CountryName (for Sender)
CountryName (for Receiver)


Any help will be greatly appreciated
 
You have a repetition:
CurrencyID - number - foreign key to Currencies - [I assume:
ForeignCurrencyID]

In case you ask more questions on this, please provide a link where one
can download the access file.

Also, imho probably this is not the best group where to make this
question.

Anyway, I propose (whatch out line splitting):

----------------------------------------------------------------------------

SELECT
T.TransactionDate AS [TransactionDate],
T.Amount AS [Amount],
C.CurrencyName AS [CurrencyName],
T.ForeignCurrencyAmount AS [ForeignCurrencyAmount],
FCurrencies.CurrencyName AS [ForeignCurrencyName],
C1.CountryName AS [CountryName],
FCountries.CountryName AS [ForeignCountryName]
FROM
(((Countries FCountries
INNER JOIN
Transactions T
ON T.ReceiverCountryID = FCountries.CountryID)
INNER JOIN
Currencies FCurrencies
ON T.ForeignCurrencyID = FCurrencies.CurrencyID)
INNER JOIN
Currencies C
ON T.CurrencyID = C.CurrencyID)
INNER JOIN
Countries C1
ON T.SenderCountryID =
C1.CountryID

----------------------------------------------------------------------------

or, if you prefer the sql92 style:

----------------------------------------------------------------------------

SELECT
T.TransactionDate AS [TransactionDate],
T.Amount AS [Amount],
C.CurrencyName AS [CurrencyName],
T.ForeignCurrencyAmount AS [ForeignCurrencyAmount],
FCurrencies.CurrencyName AS [ForeignCurrencyName],
C1.CountryName AS [CountryName],
FCountries.CountryName AS [ForeignCountryName]
FROM
Countries FCountries,
Transactions T,
Currencies FCurrencies,
Currencies C,
Countries C1
WHERE
T.ReceiverCountryID = FCountries.CountryID AND
T.ForeignCurrencyID = FCurrencies.CurrencyID AND
T.CurrencyID = C.CurrencyID AND
T.SenderCountryID = C1.CountryID


----------------------------------------------------------------------------

I hope I got right what you had in mind...

-tom


Nikolay Petrov ha scritto:
 
Back
Top