Re: subquery error with no result

[sangprabv <sangprabv@xxxxxxxxx>]

Hi,
I found the solution for this issue. I separate into 2 queries and put
the result onto an array, and do PHP array_intersect_assoc to match.
Much more simpler, fast, about 200 thousands record matched in 10
seconds. Many thanks for your helps.

Regards,


Willy 
On Thu, 2008-05-22 at 07:40 -0700, Rob Wultsch wrote:
> Sub queries suck in mysql.  It is probably worth while to post the results of:
> EXPLAIN SELECT msgdata
> FROM sent_sms
> WHERE momt = 'MT'
> AND binfo IN (SELECT
>        binfo
>        FROM sent_sms
>        WHERE momt = 'DLR')
> 
> Are binfo and momt indexed? If not, they probably should be. A
> replacement for the sub query should be something like:
> SELECT s1.msgdata
> FROM sent_sms AS s1
> 	INNER JOIN sent_sms AS s1 ON s1.binfo =s2.binfo
> WHERE s1.momt = 'MT'
> 
> 
> 
> On Thu, May 22, 2008 at 2:05 AM, sangprabv <sangprabv@xxxxxxxxx> wrote:
> > Hi,
> > Thanks for the reply. The query seems doing something but after minutes
> > of waiting it still return no result.
> >
> > Regards,
> >
> >
> > Willy
> > On Wed, 2008-05-21 at 23:06 -0400, David Lazo wrote:
> >> Would this work for you?
> >>
> >> SELECT msgdata
> >> FROM sent_sms
> >> WHERE momt = 'MT'
> >> AND binfo IN (SELECT
> >>         binfo
> >>         FROM sent_sms
> >>         WHERE momt = 'DLR')
> >>
> >>
> >> David
> >>
> >>
> >> On 5/21/08 10:30 PM, "sangprabv" <sangprabv@xxxxxxxxx> wrote:
> >>
> >> > Hi,
> >> > I tried to look for records from a table with this query:
> >> > SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = (  SELECT
> >> > binfo FROM sent_sms WHERE momt = 'DLR' )
> >> > But MySQL returns this error:
> >> > #1242 - Subquery returns more than 1 row
> >> > I tried also with ANY, IN, EXISTS.
> >> > And modified the query into:
> >> > SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') AS t1
> >> > WHERE momt = 'MT'. But none works.
> >> > What I want to view is, all records which has momt = 'MT' and binfo from
> >> > the same table where has momt = 'DLR' and has the same binfo. TIA
> >> >
> >> > Regards,
> >> >
> >> >
> >> > Willy
> >> >
> >>
> >>
> > --
> > [sangprabv]
> > http://www.sangprabv.web.id
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=wultsch@xxxxxxxxx
> >
> >
> 
> 
> 
-- 
[sangprabv]
http://www.sangprabv.web.id


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx