MYSQL, query WHERE variable = result from different query

[eternal_noob]

Table: SysproCompanyJ.dbo.InvWarehouse

+--------------+-------------+----------------+
|  Stock Code  |  QtyOnHand  |  QtyAllocated  |
+--------------+-------------+----------------+
|  C-100-01A   |     100     |       200      |
|  C-100-01B   |       0     |       100      |
|  C-200-00A   |      45     |       165      |
|  C-300-02C   |      60     |        15      |
|  C-300-02D   |      17     |        20      |
+--------------+-------------+----------------+

Table: SysproCompanyJ.dbo.InvMaster

+--------------+---------------+------------+
|  Stock Code  |  Description  |  LongDesc  |
+--------------+---------------+------------+
|   C-100-01A  |     Widget    | Bright Red |
|   C-100-01B  |  Widget, Red  |            |
|   C-200-00A  |  Shiny Widget |    Blue    |
|   C-300-02C  |  Fancy Widget |   Silver   |
|   C-300-02D  |  Fancy Widget |   Silver   |
+--------------+-------------+--------------+

Current Code:

<?php
$StockCodeA = 'C-0300-02';

$conn=odbc_connect('syspro','','');
if (!$conn)
  {exit("Connection Failed: " . odbc_error());}

$sql=
"SELECT substring(invW.StockCode, 1, (len(invW.StockCode) - 1)) as sStockCode,
SUM(invW.QtyOnHand - invW.QtyAllocated) AS Value

FROM SysproCompanyJ.dbo.InvWarehouse as invW

WHERE Warehouse = 'SW'

GROUP BY substring(invW.StockCode, 1, (len(invW.StockCode) - 1))

HAVING SUM(invW.QtyOnHand - invW.QtyAllocated) > 0";


$sql2=
"SELECT DISTINCT SysproCompanyJ.dbo.InvMaster.Description,
SysproCompanyJ.dbo.InvMaster.LongDesc

FROM SysproCompanyJ.dbo.InvMaster

WHERE SysproCompanyJ.dbo.InvMaster.StockCode LIKE '%{$StockCodeA}%'
";


$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}

$rs2=odbc_exec($conn,$sql2);
if (!$rs2)
  {exit("Error in SQL");}

echo "<table class='sortable' border='1'>
<tr>
<th>PN</th>
<th>QTY</th>
<th>DESCRIPTION</th>
</tr>";

while ($row = odbc_fetch_array($rs))
{
$Description=odbc_result($rs2,"Description");
$LongDesc = odbc_result($rs2,"LongDesc");

echo "<tr>
<td><center>{$row['sStockCode']}</center></td>
<td><center>".number_format($row['Value'], 0)."</center></td>
<td><center>{$Description}{$LongDesc}</center></td>
</tr>
"; 
}
odbc_close($conn);
echo "</table>";
?>

I received help yesterday from this forum to define $sql. In doing so some previous functionality no longer works.I need to output the description as part of the table and I'm not entirely sure on the way to do this.

 

I essentially need to execute what's currently in $sql, use sStockCode as the WHERE in $sql2 to then pull the first description it comes to in the table. I'm not sure how I set the variable to then be able to use in the second query or if I can somehow do it all within $sql. 

 

 

Desired Output:

+------------+--------+-----------------------+
|     PN     |   QTY  |      DESCRIPTION      |
+------------+--------+-----------------------+
|  C-100-01  |  -200  |   Widget Bright Red   |
|  C-200-00  |  -120  |   Shiny Widget Blue   |
|  C-300-02  |    42  |  Fancy Widget Silver  |
+------------+--------+-----------------------+

Edited February 25, 2014 by eternal_noob

[gristoi]

nested select or join:

nested select:


SELECT DISTINCT SysproCompanyJ.dbo.InvMaster.Description,

SysproCompanyJ.dbo.InvMaster.LongDesc


FROM SysproCompanyJ.dbo.InvMaster


WHERE SysproCompanyJ.dbo.InvMaster.StockCode LIKE '%(SELECT substring(invW.StockCode, 1, (len(invW.StockCode) - 1)) as sStockCode,

SUM(invW.QtyOnHand - invW.QtyAllocated)


FROM SysproCompanyJ.dbo.InvWarehouse as invW


WHERE Warehouse = 'SW'


GROUP BY substring(invW.StockCode, 1, (len(invW.StockCode) - 1))


HAVING SUM(invW.QtyOnHand - invW.QtyAllocated) > 0)%'