How can I mimic full outer join in mysql with multiple columns



  • I have two tables: A and B.

    Table A has the following set-up:

    ID date location sales
    1 2022-01-01 1 10000
    2 2022-01-02 1 10000
    3 2022-01-04 1 10000
    ... .... 2 ....

    So there is no data for for the location 1 at the date 2022-01-03.

    Table B has the following set-up:

    ID date location budget
    1 2022-01-01 1 10000
    2 2022-01-03 1 10000
    3 2022-01-04 1 10000
    ... .... 2 ....

    So there is no record for location 1 for the date 2022-01-02.

    I am trying to join the tables together to get the following output

    location sales budget
    1 30000 30000
    2 ... ...

    So I can group it on location and get | location ABC | sales 123 | budget 123 |, which is a sum of all the dates grouped, but also joined the 2 tables together on date and location.

    The query I currently have now is as follows:

    SELECT SUM(A.sales) AS sales, A.restaurant
    FROM A
    LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
    WHERE A.date between ? AND ? 
    GROUP BY A.location
    UNION
    SELECT SUM(B.budget) AS budget, B.restaurant
    FROM A
    RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
    WHERE B.date between ? AND ?  
    GROUP BY B.restaurant
    

    I've tried different types of joins and unions and ended up with a query as suggested in this https://stackoverflow.com/questions/4796872/how-can-i-do-a-full-outer-join-in-mysql to mimic a full outer join. However, with this query I get the following output:

    location column
    1 30000
    2 ...
    3 ...
    1 30000
    2 ...
    3 ...

    These sums are correct, but are not in 2 separate columns 'sales' and 'budget'.

    Is there a way to achieve this?



  • In a UNION clause the alignment of the columns in each unioned dataset defines the order of those columns in the final result set.

    Since you don't want the sales and budget data points to be in the same column, you can just un-align them and add placeholder default values in the other dataset like so:

    SELECT 0 AS budget, SUM(A.sales) AS sales, A.restaurant
    FROM A
    LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
    WHERE A.date between ? AND ? 
    GROUP BY A.location
    UNION
    SELECT SUM(B.budget) AS budget, 0 AS sales, B.restaurant
    FROM A
    RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
    WHERE B.date between ? AND ?  
    GROUP BY B.restaurant
    

    Then to get the final results you want, you'll want to do one more grouping again on location in a subquery or CTE to aggregate the different sides of the UNION into a single row like so:

    SELECT location, SUM(sales) AS sales, SUM(budget) AS budget
    FROM 
    (
        SELECT 0 AS budget, SUM(A.sales) AS sales, A.location
        FROM A
        LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
        WHERE A.date between ? AND ? 
        GROUP BY A.location
        UNION
        SELECT SUM(B.budget) AS budget, 0 AS sales, B.restaurant
        FROM A
        RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
        WHERE B.date between ? AND ?  
        GROUP BY B.restaurant
    ) Results
    GROUP BY location
    

    Note I think I fixed a typo in your first dataset of the UNION clause by changing A.restaurant to A.location.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2