Ognj3n Ognj3n - 1 year ago 38
MySQL Question

How to build one query out of two?

I have two queries that perform well when I paste them in sql console of phpmyadmin:

First query:

SET @count=0,@msisdn=00385913586990,@delivered=0;


Second query:

SELECT tmp.msisdn,max(count) FROM (
SELECT
sent_messages.msisdn,
@count := if(delivered = 0 and sent_messages.msisdn = @msisdn, @count+1, 0) as count,
@msisdn := 00385913586990,
@delivered := delivered
FROM `sent_messages`
WHERE `msisdn` LIKE '00385913586990'
AND `type` = 'PAID' ) as tmp
group by tmp.msisdn
having max(count) >= 14


When I try to execute these queries inside my php code I get blank results.

Here is how I tried to bind them together:

SELECT tmp.msisdn,max(count) FROM (
SELECT
sent_messages.msisdn,@count := 0,@msisdn=00385913586990,@delivered=0,
@count := if(delivered = 0 and sent_messages.msisdn = @msisdn, @count+1, 0) as count,
@msisdn := 00385913586990,
@delivered := delivered
FROM `sent_messages`
WHERE `msisdn` LIKE '00385913586990'
AND `type` = 'PAID' ) as tmp
group by tmp.msisdn
having max(count) >= 14


But this doesn't work.

Answer Source

Initialize the variables in a subquery that you simply cross join. This way they are initialized once. When you do it in the SELECT clause, they are set back everytime a row is read.

SELECT tmp.msisdn,max(count) FROM (
        SELECT
        sent_messages.msisdn,
        @count := if(delivered = 0 and sent_messages.msisdn = @msisdn, @count+1, 0) as count,
        @msisdn := 00385913586990,
        @delivered := delivered
        FROM  `sent_messages`
        , (SELECT @count := 0, @msisdn := '00385913586990', @delivered := 0) var_init_subquery_alias
        WHERE  `msisdn` LIKE  '00385913586990'
        AND  `type` =  'PAID' ) as tmp
        group by tmp.msisdn
        having max(count) >= 14
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download