Mysql Loop By Query Result

Question

I have this query that produces a list of foreign keys:

SELECT ad_id 
FROM ads AS i
INNER JOIN ads_earnings AS e
ON i.ad_id = e.earning_ad_id
GROUP BY i.ad_id

I want to use that as the parameter for this query in a loop:

INSERT INTO ads_stats_traffic
(traffic_ad_id,traffic_country_id,traffic_paid,traffic_verified,traffic_total) 
(
    SELECT earning_ad_id, earning_country_id, earning_paid, SUM(earning_verified), COUNT(earning_id)
    FROM ads_earnings
    WHERE earning_ad_id = ?
    GROUP BY earning_paid, earning_country_id
)

How do I do that?

Jørgen R · Accepted Answer · 2016-02-25 11:42:16Z

1

Using a nested subquery with in should work:

INSERT INTO ads_stats_traffic
(traffic_ad_id,traffic_country_id,traffic_paid,traffic_verified,traffic_total) 
(
    SELECT earning_ad_id, earning_country_id, earning_paid, SUM(earning_verified), COUNT(earning_id)
    FROM ads_earnings
    WHERE earning_ad_id in
    (
        SELECT ad_id 
        FROM ads AS i
        INNER JOIN ads_earnings AS e
        ON i.ad_id = e.earning_ad_id
        GROUP BY i.ad_id
    )
    GROUP BY earning_paid, earning_country_id
)

answered Feb 25, 2016 at 11:42

Jørgen R

10.8k7 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amy Neville Over a year ago

Ohhhhh IN.....new SQL word for me......Just going to take my dog walkies and then I try it out :)

Collectives™ on Stack Overflow

Mysql Loop By Query Result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related