February 18, 2009 at 8:19 am
Hello!
I am working on a query that combines 3 tables. It is based on a risk analyses, where I take the portfolio information from table 1, the riskprofile from table2, and the riskmodel from table3.
In the portfolio can be funds that are not in the riskmodel, and the other way around.
I got to the point that I have the complete portfolio, with al the riskmodel-weights. But the funds that are in the riskmodel, but not in the portfolio are not showing.
What can I do?
SELECT Sheet1.portcode, Sheet1.symbol, Sheet3.weight
FROM ((Sheet1 Sheet1 INNER JOIN
Sheet2 ON Sheet1.portcode = Sheet2.portcode) LEFT OUTER JOIN
Sheet3 ON Sheet1.symbol = Sheet3.symbol)
WHERE (Sheet1.portcode = 400004)
Cheers!
Martijn
February 18, 2009 at 8:35 am
Please read the articles linked in my signature for how to post in order to get better answers. I will attempt to answer your question, but without the information suggested in the links it will be hard.
I think what you are saying is that there can be data in the Portfolio table that does have a matching row in the RiskModel table and data in the RiskModel table that does not have a matching row in the Portfolio table.
In this case I believe you want a FULL OUTER JOIN like this on the RiskModel table:
SELECT
Portfolio.portcode,
ISNULL(Portfolio.symbol, RiskModel.symbol) AS symbol,
RiskModel.weight
FROM
Portfolio INNER JOIN
RiskProfile
ON Portfolio.portcode = RiskProfile.portcode FULL OUTER JOIN
RiskModel
ON Portfolio.symbol = RiskModel.symbol
WHERE
(Portfolio.portcode = 400004)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 8:59 am
Thank you very much, I am new to this kind of work... I will do my homework 🙂
I have created an Access Database with this information, because the riskmodels are only stored in excel files. So I have combined some data from our administration system and the excelsheets into an AccessDB.
There is indeed data in the portfolio-table that matches data in the Riskmodel, but also data that doesnt matches (the query below show the matched and unmatched data).
But in the Riskmodel there is also data than can be or can be not in the portfolio-table, but that is not showing.
Now I have tried to alter your query, and use the full join, but access doesnt allow me to do that. And I have tried VisualStudio with the same results.
Sheet1= portfolio
Sheet2=Riskprofile
Sheet3=Riskmodel
SELECT Sheet1.portcode, Sheet1.symbol, Sheet3.weight
FROM ((Sheet1 Sheet1 INNER JOIN
Sheet2 ON Sheet1.portcode = Sheet2.portcode) LEFT OUTER JOIN
Sheet3 ON Sheet1.symbol = Sheet3.symbol)
WHERE (Sheet1.portcode = 400004)
portcodesymbol weight
400004TEGACEH LX5
400004CAEUBAC LX
400004FIDEBFA LX5
400004cash
400004AETASEI LX2
400004FASTEUR LX3
400004HENPEA2 LX3
400004RGU LN
400004TDNESG1 LN7
This is the query with the errors:
SELECT Sheet1.portcode, ISNULL(Sheet1.symbol, Sheet3.symbol) AS symbol,
Sheet3.weight
FROM ((Sheet1 INNER JOIN
Sheet2 ON Sheet1.portcode = Sheet2.portcode) FULL OUTER JOIN
Sheet3 ON Sheet1.symbol = Sheet3.symbol)
WHERE (Sheet1.portcode = 400004)
February 18, 2009 at 9:19 am
Not enough information to really help you. May I suggest that you read the first article I have referenced below in my signature block regarding asking for assistance.
If you follow the guidelines in that article regarding posting DDL for your tables, sample data for the tables, and the expected results based on the sample data will greatly help you in getting answers that really help you.
February 18, 2009 at 9:28 am
Some sample data:
Sheet1:
portcodesymbol MarketValue
400004TEGACEH LX 107.002,20
400004CAEUBAC LX 86.849,00
400004FIDEBFA LX 108.564,48
400004cash 144.288,47
Sheet2:
portcodetypeprofiel
400004BeheerEvenwichtig
Sheet3:
symbol weightProfiel
TEGACEH LX5,5Evenwichtig
FIDEBFA LX5,5Evenwichtig
IBGX NA 4,125Evenwichtig
Wanted output:
portcodesymbol Weight MarketValue
400004TEGACEH LX 5.5 107.002,20
400004CAEUBAC LX 0 86.849,00
400004FIDEBFA LX 5.5 108.564,48
400004 IBGX NA 0 0
February 18, 2009 at 9:35 am
martijnschaar (2/18/2009)
Some sample data:Sheet1:
portcodesymbol MarketValue
400004TEGACEH LX 107.002,20
400004CAEUBAC LX 86.849,00
400004FIDEBFA LX 108.564,48
400004cash 144.288,47
Sheet2:
portcodetypeprofiel
400004BeheerEvenwichtig
Sheet3:
symbol weightProfiel
TEGACEH LX5,5Evenwichtig
FIDEBFA LX5,5Evenwichtig
IBGX NA 4,125Evenwichtig
Wanted output:
portcodesymbol Weight MarketValue
400004TEGACEH LX 5.5 107.002,20
400004CAEUBAC LX 0 86.849,00
400004FIDEBFA LX 5.5 108.564,48
400004 IBGX NA 0 0
I'm guessing that you haven't read the article. You haven't provided the DDL for the tables (as far as I can see) as CREATE statements, nor is the above sample data provided in a format that any of us can readily use in SSMS to populate your tables using INSERT statements.
You need to help us help you. We are not paid to help the people that come to this site, we do it for free to help those who need help and in return for the help we ourselves have received from this site as well.
February 18, 2009 at 9:41 am
I have read it... but as I wrote before I am new to this stuff, and I hardly understand what is wanted and needed for a good post.
But thanks for the effort.
February 18, 2009 at 9:52 am
If I have some time later today, I might try and do more. Unfortunately, I do have my own job that I need to do as well.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply