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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
>
>
--
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
