CODEWITHSUNDEEP
phpmysqlsql
purpletree
purpletree

Reputation: 1943

Confused on how to combine results from two queries

Query 1:

SELECT count(id) as conversions, SUM(user_payout) as amount, geoip.country_name, geoip.country_code
FROM conversions
LEFT JOIN geoip ON conversions.end_ip BETWEEN geoip.start_long AND geoip.end_long
WHERE user_id = 1
AND type != ''
AND status = 1
AND month(created_at) = month(now())
GROUP BY country_name
ORDER BY country_name ASC

Results:
Query results

Query 2:

SELECT count(id) as clicks, geoip.country_name, geoip.country_code
FROM clicks
LEFT JOIN geoip on clicks.ip BETWEEN geoip.start_long AND geoip.end_long
WHERE user_id = 1
AND month(created_at) = month(now())
GROUP BY country_name
ORDER BY country_name ASC

Results:
Query results

I'd like to combine the two so for each country, it shows clicks, conversions, country_name and country_code.

Can't figure out how to do this.

Thanks!

Upvotes: 3

Views: 107

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can combine them using subqueries and left outer join:

select clicks.country_code, clicks.country_name,
       coalesce(clicks.clicks, 0) as clicks,
       coalesce(conversions.conversions, 0) as conversions
from (<subquery 2>) clicks left outer join
     (<subquery 1>) conversions
     on clicks.country_code = conversions.country_code;

This assumes that all countries have at least one click.

EDIT:

If the list of countries is in geo_ip, you can do:

select gi.country_code, gi.country_name, clicks.clicks, conversions.conversions
from (select distinct country_code, country_name
      from geo_ip
     ) gi left outer join
     (<subquery 2>) clicks
     on clicks.country_code = gi.country_code left outer join
     (<subquery 1>) conversions
     on conversions.country_code = gi.country_code;

Upvotes: 2

Sewder
Sewder

Reputation: 754

Left Join clicks on the first query to geoip.

Or you could use a union but you would have go get each result to only show clicks, conversions, country_name and country_code by only selecting those in the select statement.

Upvotes: 0

Related Questions