Help with a join, or perhaps another method.

[dcreeves]

Hi All, hopefully someone can lend me some insight with a problem I'm stuck with.

 

What I have is two tables.

 

TABLE: investmentOfferings

id: Unique identifier

class: Class of offering (A, or AA)

year: Year of offering

name: Friendly name of offering

units: Amount of units added in this offering

dollarPerUnit: Dollar value of one unit in this offering

warrant: Boolean for warrant or no (0=no 1=yes)

 

TABLE: actualInvestments

id: Unique identifier

investor: Identifies investor by referencing investors>id

dollarAmount: Dollar amount invested in this offering

class: Identifies class by referencing investmentOfferings>id

 

The structure of the application is so. investmentOfferings creates 4 fundraisers. Each year a new one. The actualInvestments table is the investment contributed per offering. Essentially what I need to do is to create a report, per investor(investors have a third separate table) that shows the amount invested per offering. The kicker being: not every investor will invest every offering, but I still needs to show a 0 dollar amount. I understand that I could create separate 0 dollar  investments for each investor, but that seems impractical. Especially where there will be more offerings once I'm finished working on this. In terms of the report, here's what I'm doing to output the data to a table.

 

<?php do {?>
  <tr>
    <td><?php echo $row_offerings['name']?></td>
    <td><?php echo $row_offerings['dollarAmount'];?></td>
<?php while ($row_offerings = mysql_fetch_assoc($offerings)); ?>

 

This creates a new column in the table for each part of my array. The issue I think is in my query.

"SELECT * FROM actualinvestments AS a RIGHT JOIN investmentofferings AS i ON i.id=a.offering WHERE class='AA' AND investor=" . $investorID . " OR investor IS NULL"

Ive tried variations on this, but cannot seem to find anything that will produce currently entered investments, and placeholders for offerings with no investments. Could anyone steer me in the right direction? Any help would be greatly appreciated.

[sunfighter]

You don't want investmentOfferings in your JOIN. InvestmentOfferings will give you the info to write the main title for your report table followed by all of your investors and how much they invested into that title or investmentOfferings name. So your JOIN is between the investors in the third separate table and actualInvestments

 

See if this works:

 

SELECT * FROM investors

LEFT OUTER JOIN actualInvestments

ON investors.id = actualInvestments.investor

WHERE actualInvestments.class = investmentOfferings.id

[dcreeves]

Thanks for the reply sunfighter. I ran your query and I'm receiving the following error.

 

Unknown column 'investmentOfferings.id' in 'where clause'

 

I assume this is because the investmentOfferings table is never selected in the query. Only investors and actualInvestments is. Keeping that in mind, what I'm essentially trying to do, if it makes it any clearer:

 

-Use investmentOfferings.name to dynamically produce a title for each row (offering 1, offering 2, etc)

-Use actualInvestments to populate data per offering. Leaving a 0 or even null record in the case that the user never invested in the given offering. (Keeping in mind, that each report is investor specific. That user is selected by ID earlier in the program)

-Populate all this data using that do/while function to create a new row for every offering added.

 

Thanks again

[dcreeves]

Anyone? I hate to bump like this, but I'm totally stumped here :/

[sunfighter]

dcreeves,

When you query the investmentOfferingstable to get the heading, you should also get the id. make this a variable ($investId for ie.) sub that into the join I gave you for the investmentOfferings.id in the WHERE line.

 

This is not the most elegant solution but it should work. I think. Just check it out.

 

And I hope a mysql exspert comes along.