Retrieving Data From Multiple Tables

[Icebergness]

Hi,

 

I work in a Stockbroker's and I'm trying to display our client's holdings from my MSSQL Database. To put it in to perspective...

 

The page displays a single client. A client has multiple holdings. Each holding relates to a single stock, and each stock is linked to a single currency.

 

So what I need is something like:

 

Bob Marley's Holdings

 

Holding 1: Apple | USD | 1.6549

Holding 2: Google | EUR | 1.2525

 

The code I have so far is:

<?php

$client_ref = "50000";

$holding_sql = "SELECT * FROM [HOL_CORE] INNER JOIN [sTO_CORE] on [HOL_CORE].[sTOCK REC NO] = [sTO_CORE].[sTOCK ID] WHERE [HOL_CORE].[CLIENT REC NO] = '$client_ref' AND [HOL_CORE].[QTY HELD] != '0'";
$holding_result = sqlsrv_query($connnection, $holding_sql);
while($holding = sqlsrv_fetch_array($holding_result, SQLSRV_FETCH_ASSOC))

{

    $stock_currency = $holding['CURRENCY'];
 $stock_name = $holding['SHORT NAME'];

 echo $stock_name . ' | ' . $stock_currency . ' | ' . '<br>';

}

?>

 

Now I need to get the exchange rate from a table called [CUR_CORE] for each stock, where [CURRENCY ABBREV] = $stock_currency. Does anyone know how I would go about retrieving this?

 

Cheers!

 

Dave

[Christian F.]

Just inner join it, like you've done on the STO_CORE table. Only don't use the PHP variable, but the SQL field from which you've retrieved the value assigned to that variable.

[Icebergness]

Either Google is getting worse, or I'm getting worse at using Google? (Probably the latter!)

 

That worked perfectly. I'd read somewhere that multiple joins couldn't be done - obviously they can Thank you for your help!

[Christian F.]

You're welcome, glad I could help.