Welcome to WebmasterWorld Guest from 50.16.112.199

Forum Moderators: open

Nested MySQL statement issues: "Unable to save result set" error.

   
6:19 am on Dec 12, 2011 (gmt 0)

5+ Year Member



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,
11:11 pm on Dec 13, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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';
5:32 am on Dec 14, 2011 (gmt 0)

5+ Year Member



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.
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month