-4

I need a Full Outer Join in mysql. I found a solution here: Full Outer Join in MySQL My problem is that t1 and t2 are subqueries themselves. So resulting query looks like a monster. What to do in this situation? Should I use views instead of subqueries?

Edit: I'll try to explain a bit more. I have orders and payments. One payment can cower multiple orders, and one order can be cowered by multiple payments. That is why I have tables orders, payments, and paymentitems. Each order has field company (which made this order) and manager (which accepted this order). Now I need to group orders and payments by company and manager and count money. So I want to get something like this:

company1 | managerA | 200  | 200  | 0
company1 | managerB | Null | 100  | 100
company1 | managerC | 300  | Null | -300
company2 | managerA | 150  | Null | -150
company2 | managerB | 100  | 350  | 250

The query, I managed to create:

SELECT coalesce(o.o_company, p.o_company)
     , coalesce(o.o_manager, p.o_manager)
     , o.orderstotal
     , p.paymentstotal
     , (coalesce(p.paymentstotal, 0) - coalesce(o.orderstotal, 0)) AS balance
FROM
         (((/*Subquery A*/SELECT orders.o_company
                 , orders.o_manager
                 , sum(o_money) AS orderstotal
            FROM
              orders
            WHERE
              (o_date >= @startdate)
              AND (o_date <= @enddate)
            GROUP BY
              o_company
            , o_manager) AS o
 LEFT JOIN (/*Subquery B*/SELECT orders.o_company
                , orders.o_manager
                , sum(paymentitems.p_money) AS paymentstotal
           FROM
             ((payments
           INNER JOIN paymentitems
           ON payments.p_id = paymentitems.p_id)
           INNER JOIN orders
           ON paymentitems.p_oid = orders.o_id)
           WHERE
             (payments.p_date >= @startdate)
             AND (payments.p_date <= @enddate)
           GROUP BY
             orders.o_company
           , orders.o_manager) AS p
    ON (o.o_company = p.o_company) and (o.o_manager = p.o_manager))
  union 
           (/*Subquery A*/ 
 right join /*Subquery B*/ 
    ON (o.o_company = p.o_company) and (o.o_manager = p.o_manager)))

This is simplified version of my query. Real query is much more complex, that is why I want to keep it as simple as it can be. Maybe even split in to views, or may be there are other options I am not aware of.

Community
  • 1
  • 1
Torbins
  • 2,111
  • 14
  • 16
  • Do your resulting query have to participate in the SQL queries beauty contest? If not, why bother about looks? Check its performance, apply the necessary indexing etc. – raina77ow Mar 22 '13 at 15:09
  • You may want to show us your query. – Kermit Mar 22 '13 at 15:09
  • @raina77ow By the term "beauty" I mean code easy to understand, edit and debug. My current project is long term project, so this characteristics are desirable. – Torbins Mar 22 '13 at 19:06
  • Do you have this in a stored procedure or do you generate it with code? If you do it with code, you can hide much of the sql "ugliness". – Anders Lindén Apr 01 '13 at 09:46
  • That was a stored procedure. – Torbins Apr 03 '13 at 18:58

2 Answers2

0

If you are trying to do a full outer join and the relationship is 1-1, then you can accomplish the same thing with a union and aggreagation.

Here is an example, pulling one column from two different tables:

select id, max(col1) as col1, max(col2) as col2
from ((select t1.id, t1.col1, NULL as col2
       from t1
      ) union all
      (select t23.id, NULL as col1, t2.col2
       from t2
      )
     ) t
group by id
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I think the clue is in "group orders and payments by company". Break the outer join into a query on orders and another query on payments, then add up the type of money (orders or payments) for each company.

Mark Leighton Fisher
  • 5,609
  • 2
  • 18
  • 29
  • That is exactly what I did. Now all grouping logic has been moved to client side and handled by OLAP-component FastCube. – Torbins Mar 30 '13 at 11:14