Join in MySQL

Databases
1

Hi Everyone,

Have a Problem with this; I have two tables

 1) prod_detail

material_code   material_desc
  A001                  desc1
  A003                  desc2
  H001                  desc3
  H002                  desc4


2) temp_detail

dist_id    material_code   purchase_quantity    sales_quantity    date
152305        H001                 15                  0         2010-01-01
152305        H001                  0                  10       2010-01-06
152311        A001                  20                 0        2010-01-08
152311        A001                  0                  2        2010-01-10

I want a query which can give me the following as result

dist_id    material_code   purchase_quantity    sales_quantity    date
152305        H001                 15                    0        2010-01-01
152305        H001                  0                    10       2010-01-06
152305        H002                  null                 null      null
152305        A001                  null                 null       null
152305        A003                  null                 null      null
152311        A001                  20                  0         2010-01-08
152311        A001                  0                    2         2010-01-10
152311        A003                  null                 null       null
152311        H001                  null                 null       null
152311        H002                  null                null        null

How can I get this ? Stuck in this. Please help me out . Thanks in Advance

2

Looks complicated as it requires any prod_detail that’s not in the same dist_id.

Is there a reason your data need to be so complicated? Perhaps the purchase and sales should be two tables?

3 
SELECT z.dist_id
     , z.material_code
     , t.purchase_quantity
     , t.sales_quantity
     , t.date
  FROM ( SELECT x.dist_id
              , y.material_code
           FROM ( SELECT DISTINCT dist_id
                    FROM temp_detail ) AS x
         CROSS
           JOIN ( SELECT material_code
                    FROM prod_detail ) AS y
       ) AS z
LEFT OUTER
  JOIN temp_detail AS t
    ON t.dist_id = z.dist_id
   AND t.material_code = z.material_code

“not tested” :wink:

4

Does’nt matter. It works wonderfully correct. I have understood the way you have done it clearly as well. Thanks a lot:cool: