doing first 2 inner joins followed by a left outer join on 3 tables

[keeper85]

I have 3 tables (called Dates, Bond Deals and EICC pricing) in Access in SQL and I am trying to create the following query with 3 joints. First I am trying to do two inner joints, one between the Dates table Bond Deals table using columns Data Date and Data Date and one between Dates and EICC pricing using columns Price Date (EICC) and PriceDate. Finally I want to do a left outer join on the outcome of the two, where everything from the first inner joint query is included. I put together the following code, however, it does not work. I understand that the problem lies with the last part of the code, however, do not know how to fix it. Can anyone help me?

From (Dates  INNER JOIN [Bond deals] on [Bond Deals].[Data Date] = Dates.[Data Date]) LEFT OUTER JOIN ([EICC pricing] INNER JOIN Dates on Dates.[Price Date (EICC)]= [EICC pricing].[PriceDate]) on  [Bond deals].Bond = ([EICC pricing] INNER JOIN Dates on Dates.[Price Date (EICC)]= [EICC pricing].[PriceDate]).Bonds_ShortName

 

[Kyle123]

You haven't said what you want to left join on so I'm a little confused. As a first guess I think you might want something like:

SELECT *

FROM [dates]

INNER JOIN [bond deals]

ON [dates].[data date] = [bond deals].[data date]

INNER JOIN [eicc pricing] A

ON dates.[price date (eicc)] = A.[pricedate]

RIGHT OUTER JOIN [eicc pricing] B

ON B.[somebondreference?] = [bond deals].[bond]

 

[keeper85]

Hi Kyle, thanks for the response. though not really sure how to use your code (what are A and B supposed to mean?). Practically what I am trying to achieve is to do two inner joins between two tables and then in the following step do a left outer join on the outcome of the two inner joins.
I have 3 tables (called Dates, Bond Deals and EICC pricing).
First I am trying to do two inner joints, one between the Dates table and the Bond Deals table using fields Data Date and Data Date.
Then another Inner Join between the Dates table (again) and EICC pricing using fields Price Date (EICC) and PriceDate.
Finally I want to do a left outer join on the outcome of the two, where everything from the first inner joint query (so from the inner join of Dates and Bond deals table) is included.
Hope this helps clarify what I am looking for.

 

[Kyle123]

Isn't that what my sql does?

You just need to choose the field name that you want to join the EICC table on to the results of the previous query (somebondreference here)

A and B are table aliases, they let you use the same table separately within a single query