Combining SQL SELECT statements 1

[chipboard]

Hi all - first post and I hope I have the right section of the forum...

I have 3 seperate SELECT statements that I would like to combine into one. I have four Tables called "Client", "Letters", "Agreement" and "Rate", which are linked in that order via ID values. Rate also links to a number of other tables. They are all one-to-many relationships from left to right, but the Rate to the other tables is Many-To-One. I understand how to use Inner Join to join two together but would like to know how to join more than two tables together.

These are the SQL statements that I have already and hopefully someone can help me combine them because I can't seem to find the best method anywhere:

SELECT 
Client.ClientName, 
Letters.LetterReference

FROM Letters
INNER JOIN Client
ON Client.ClientId = Letters.ClientId

SELECT 
Letters.LetterReference, 
Agreement.AgreementReference

FROM Agreement
INNER JOIN Letters
ON Letters.LetterId = Agreement.LetterId

SELECT 
Agreement.AgreementReference, 
Instrument.Instrument, 
Underlying.Underlying, 
Rate.RateQualifier, 
Rate.Notes, 
LowerRuleType.LowerRuleType, 
Rate.LowerRule, 
Rate.LowerRuleUnitId, 
UpperRuleType.UpperRuleType, 
Rate.UpperRule, 
Rate.UpperRuleUnitId, 
Rate.Rate, 
Units.Unit, 
RateType.RateType

FROM (((((((Rate
INNER JOIN Agreement
ON Rate.AgreementId = Agreement.AgreementId)
INNER JOIN Instrument
ON Rate.InstrumentId = Instrument.InstrumentId)
INNER JOIN Underlying
ON Rate.UnderlyingId = Underlying.UnderlyingId)
INNER JOIN RateType
ON Rate.RateTypeId = RateType.RateTypeId)
INNER JOIN Units
ON Rate.RateUnitId = Units.UnitId)
INNER JOIN LowerRuleType
ON Rate.LowerRuleTypeId = LowerRuleType.LowerRuleTypeId)
INNER JOIN UpperRuleType
ON Rate.UpperRuleTypeId = UpperRuleType.UpperRuleTypeId

Can anyone help please?

Thanks very much,

 

[chipboard]

In case it helps, I am writing this query in Microsoft Query for the purposes of pulling data from MS Access into MS Excel...

 

[lespaul]

so you want to put all these queries together into one?

like this?

SELECT 
Client.ClientName, 
Letters.LetterReference,
Agreement.AgreementReference, 
Instrument.Instrument, 
Underlying.Underlying, 
Rate.RateQualifier, 
Rate.Notes, 
LowerRuleType.LowerRuleType, 
Rate.LowerRule, 
Rate.LowerRuleUnitId, 
UpperRuleType.UpperRuleType, 
Rate.UpperRule, 
Rate.UpperRuleUnitId, 
Rate.Rate, 
Units.Unit, 
RateType.RateType

FROM Rate
INNER JOIN Agreement
ON Rate.AgreementId = Agreement.AgreementId
INNER JOIN Letters
ON Letters.LetterId = Agreement.LetterId
INNER JOIN Client
ON Client.ClientId = Letters.ClientId
INNER JOIN Instrument
ON Rate.InstrumentId = Instrument.InstrumentId
INNER JOIN Underlying
ON Rate.UnderlyingId = Underlying.UnderlyingId
INNER JOIN RateType
ON Rate.RateTypeId = RateType.RateTypeId
INNER JOIN Units
ON Rate.RateUnitId = Units.UnitId
INNER JOIN LowerRuleType
ON Rate.LowerRuleTypeId = LowerRuleType.LowerRuleTypeId
INNER JOIN UpperRuleType
ON Rate.UpperRuleTypeId = UpperRuleType.UpperRuleTypeId

have you read Understanding SQL Joins

Leslie

 

[chipboard]

Thanks Lespaul,

I had seen the page you linked before but I couldn't see a close enough example to what I was trying to do to figure it out.

With the way you wrote my code I have tried that and it is working fine so thanks for that. I was trying to make it more complicated than it is and I can see how you've done it so that's great - I didn't think it was that easy!

Thanks for your help again,

 

[lespaul]

no problem! thanks for the star!

Leslie