Welcome to WebmasterWorld Guest from 50.19.0.90

Forum Moderators: open

Message Too Old, No Replies

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

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

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 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,
11:11 pm on Dec 13, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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';
5:32 am on Dec 14, 2011 (gmt 0)

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members