homepage Welcome to WebmasterWorld Guest from 54.161.236.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved