(Financial Program) Doing a left join

[timely]

I am sure this has been awnsered several times here but I am trying to get some quick help on something that should not be taking me this long. Ok then, I have worked with Oracle and was use to outer joins. I am trying to join two tables to create a query for a form.

One table is the table with all the information on how we will allocate funds for our investors. This will have information of the company the product come from (Company), the risk level that it is reporting on (risk), (product), and percent of allocation. This is the allocation table.

The other table is what the investor is currently in. In this we have investor, invester number(invno), account number of product where each product number is unique for each investor and each product he has acctno, product, amount, and risk.

I am trying to get it so that I can get the product, with a spacific company and risk level of allocation and the allocations from the first table and then each client for all the products even if the product is not in the second table for them and want it to come up showing that they have a null if they do not have the product. So that I can match up what they have in product and amount owned and besides it the allocation requirements.

I have been trying left joins on both the product and risk group but neither leaves a null on account number or amount. I know that given products are not on the second table for an investor in a given risk group but it needs to show up so that we can allocate their accounts correctly.

Thanks
Tim

 

[fullkeel]

Perhaps your join is set up to include only those rows where the joined fields of both tables are equal, you should maybe change the join properties to include all the records from one table and only those records from the second table where the join fields are equal.

 

[timely]

Okay, I have tried all kind of ways but still no luck. Here is the code I have now. I need for all the products from tbl_ff_portfolio to show up with their percentages, combined with zcustrep to show how much they own already of a given product. What is not showing are the tickers of the products that are need from tbl_ff_portfolio but are not in zcustrep.



SELECT zcustrep.CORRNAME, tbl_ff_portfolio.[Fund Family ID], tbl_ff_portfolio.Portfolio, tbl_ff_portfolio.Ticker, tbl_ff_portfolio.Percent, zcustrep.mktvalue
FROM tbl_ff_portfolio LEFT JOIN zcustrep ON ( tbl_ff_portfolio.Ticker = zcustrep.mktcode)
Where tbl_ff_portfolio.[Fund Family ID]=8 AND tbl_ff_portfolio.Portfolio="7B"
GROUP BY zcustrep.CORRNAME, tbl_ff_portfolio.[Fund Family ID], tbl_ff_portfolio.Portfolio, tbl_ff_portfolio.Ticker, tbl_ff_portfolio.Percent, zcustrep.mktvalue;


CORRNAME Fund Family ID Portfolio Ticker Percent mktvalue
Daniel P. Cassidy 8 7B +ASVIX 15.00% 6044.71
Daniel P. Cassidy 8 7B +ATCIX 10.00% 0
Daniel P. Cassidy 8 7B +BPRXX 10.00% 3621.47
Daniel P. Cassidy 8 7B +BTTRX 15.00% 3209.09
Daniel P. Cassidy 8 7B +BTTTX 15.00% 3194.79
Daniel P. Cassidy 8 7B +TWVLX 10.00% 7477.07

This is an example report and I know on the profolio table that there is another ticker with 15% and one with 10% .