Search code examples
mysqlsqljoinsubquery

Joining 2 SQL SELECT result into one query

I wanted to know if there's a way to join two or more result sets into one. i have the following two queries First query:

SELECT
  CONCAT(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on)) as day_month_year,
  db.country.country ,
  count(concat(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on))) as count ,
  COUNT(DISTINCT db.prod_id.email) AS MAIL
from db.prod_id
left join db.country on db.prod_id.branch_id = db.country.id
where  db.prod_id.created_on > '2020-11-17'  and (db.country.type = 1 or db.country.type = 2)
group by
  concat(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on)),
  db.country.country
order by db.prod_id.created_on

The second query:

select
  CONCAT(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on)) as day_month_year,
  db.country.country,
  count(CONCAT(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on))) as count_BUY
from db.prod_id
left join db.prod_evaluations on db.prod_id.id = db.prod_evaluations.id
left join db.country on db.prod_id.branch_id = db.country.id
left join (Select prod_properties.prod_id, prod_properties.value From prod_properties Where prod_properties.property_id = 5) as db3 on db3.prod_id = db.prod_id.id
where db.prod_id.created_on > '2020-11-17'
and db3.value  = 'online-buy' and db.prod_id.status_id <> 25
group by
  concat(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on)),
  db.country.country
order by db.prod_id.created_on

The first query give the following result:

+------------+---------+-------+------+
|    day     | Country | Count | Mail |
+------------+---------+-------+------+
| 17-11-2020 | IT      |   200 |  100 |
| 17-11-2020 | US      |   250 |  100 |
| 18-11-2020 | IT      |   350 |  300 |
| 18-11-2020 | US      |   200 |  100 |
+------------+---------+-------+------+

The second query give:

+------------+---------+-----------+
|    day     | Country | Count_BUY |
+------------+---------+-----------+
| 17-11-2020 | IT      |        50 |
| 17-11-2020 | US      |        70 |
| 18-11-2020 | IT      |       200 |
| 18-11-2020 | US      |        50 |
+------------+---------+-----------+

Now i want to merge these two result in one:

+------------+---------+-------+------+-----------+
|    day     | Country | Count | Mail | Count_BUY |
+------------+---------+-------+------+-----------+
| 17-11-2020 | IT      |   200 |  100 |        50 |
| 17-11-2020 | US      |   250 |  100 |        70 |
| 18-11-2020 | IT      |   350 |  300 |       200 |
| 18-11-2020 | US      |   200 |  100 |        50 |
+------------+---------+-------+------+-----------+

How can i perform this query? I'm using mysql Thanks

Solution

  • The simple way: You can join queries.

    select *
from ( <your first query here> ) first_query
join ( <your second query here> ) second_query using (day_month_year, country)
order by day_month_year, country;

    This is an inner join. You can also outer join of course. MySQL doesn't support full outer joins, though. If you want that, you'll have to look up how to emulate a full outer join in MySQL.

    The hard way ;-) Merge the queries.

    If I am not mistaken, your two queries can be reduced to

    select
  date(created_on),
  branch_id as country,
  count(*) as count_products,
  count(distinct p.email) as count_emails
from db.prod_id
where created_on >= date '2020-11-17'
and branch_id  in (select country from db.country where type in (1, 2))
group by date(created_on), branch_id
order by date(created_on), branch_id;

    and

    select
  date(created_on),
  branch_id as country,
  count(*) as count_buy
from db.prod_id
where created_on >= date '2020-11-17'
and status_id <> 25
and prod_id in (select prod_id from prod_properties where property_id = 5 and status_id <> 25)
group by date(created_on), branch_id
order by date(created_on), branch_id;

    The two combined should be

    select
  date(created_on),
  branch_id as country,
  sum(branch_id in (select country from db.country where type in (1, 2)) as count_products,
  count(distinct case when branch_id  in (select country from db.country where type in (1, 2) then p.email end) as count_emails,
  sum(status_id <> 25 and prod_id in (select prod_id from prod_properties where property_id = 5 and status_id <> 25)) as count_buy
from db.prod_id
where created_on >= date '2020-11-17'
group by date(created_on), branch_id
order by date(created_on), branch_id;

    You see, the conditions the queries have in common remain in the where clause and the other conditions go inside the aggregation functions.

    sum(boolean) is short for sum(case when boolean then 1 else 0 end), i.e. this counts the rows where the condition is met in MySQL.