| Nested MySQL statement issues: "Unable to save result set" error.
|
nelsonm
msg:4396902 | 6:19 am on Dec 12, 2011 (gmt 0) |
HI all,
tableA record contains two foreign record key references to tableB.
I'd like to select itemR1, priceR1, itemR2, priceR2 FROM tableB.
However, i keep getting the "Unable to save result set" error.
This is the code segment:
<?php
$sql = 'SELECT ip.SVID, ip.ITID, ip.Item AS ItemA, ip.Price AS PriceA, it.itQTY AS QtyA,
(SELECT ip.Item
FROM `it-pricelist` AS ip
INNER JOIN `items` AS it ON it.AIID = ip.ITID
WHERE it.WOID = '.$row[0].') AS ItemB,
(SELECT ip.Price
FROM `it-pricelist` AS ip
INNER JOIN `items` AS it ON it.AIID = ip.ITID
WHERE it.WOID = '.$row[0].') AS PriceB,
it.aiQTY AS QtyB';
$sql .= ' FROM `it-pricelist` AS ip';
$sql .= ' INNER JOIN `items` AS it ON it.ITID = ip.ITID';
$sql .= ' WHERE it.WOID = '.$row[0]; // $row[0] contains the ID of the workorder record queried in the previous select.
$sql .= ' ORDER BY ip.ITID';
?>
Removing the nested select's removes the error but does not give me all the data i need.
In any case, I can't see my error. Can anyone help?
thanks,
|
Dijkgraaf
msg:4397596 | 11:11 pm on Dec 13, 2011 (gmt 0) |
Question: Why are you using a nested select in the first place? Just join the ip-pricelis table again with another alias as below.
$sql = 'SELECT ip.SVID, ip.ITID, ip.Item AS ItemA, ip.Price AS PriceA, it.itQTY AS QtyA, ip2.Item AS ItemB, ip2.Price AS PriceB, it.aiQTY AS QtyB';
$sql .= ' FROM `it-pricelist` AS ip';
$sql .= ' INNER JOIN `items` AS it ON it.ITID = ip.ITID';
$sql .= ' INNER JOIN `it-pricelist` AS ip2 ON it.ITID = ip2.AIID';
$sql .= ' WHERE it.WOID = '.$row[0]; // $row[0] contains the ID of the workorder record queried in the previous select.
$sql .= ' ORDER BY ip.ITID';
|
nelsonm
msg:4397681 | 5:32 am on Dec 14, 2011 (gmt 0) |
While i studied the MySQL docs a bit, i never considered that you could from and join the same table or join the same table twice.
thanks.
|
|
|
