Skip navigation links

MySQL Forums :: Newbie :: In Subquery with Comma Separated String


Advanced Search

In Subquery with Comma Separated String
Posted by: iceangel89 LLL ()
Date: March 24, 2009 10:02PM

i am running this query

select sum(quantity), productid
from salesItems a, sales b
where mSalesID=52415
and a.salesID=b.salesID
and a.outletCode=b.outletCode
and customerCode='MV144653'
and productid in (select mid(packageitem, 2, length(packageitem)-2) from products where productid=54)
group by productid

the subquery

select mid(packageitem, 2, length(packageitem)-2) from products where productid=54

gives 1 row, "38,20,7"

but it seems, when using it in a subquery, mysql only evaluates it to be

select sum(quantity), productid
from salesItems a, sales b
where mSalesID=52415
and a.salesID=b.salesID
and a.outletCode=b.outletCode
and customerCode='MV144653'
and productid in (38) << so 20 and 7 missing
group by productid

i figured out this is probably due to the fact that mysql will convert the string to an int so "38"

how can i fix this so that mysql will evalaue the subquery correctly and select records where productid 38,20,7

Options: ReplyQuote


Subject Written By Posted
In Subquery with Comma Separated String iceangel89 LLL 03/24/2009 10:02PM
Re: In Subquery with Comma Separated String Jay Alverson 03/25/2009 08:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.