SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Join in MySQL

  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join in MySQL

    Hi Everyone,

    Have a Problem with this; I have two tables

    Code:
     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

    Code:
    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
    Last edited by vigneshr35; Feb 3, 2010 at 00:07. Reason: Table was not looking clear.
    Thank You
    Known is a Drop, Unknown is an Ocean

  2. #2
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    "not tested"
    Does'nt matter. It works wonderfully correct. I have understood the way you have done it clearly as well. Thanks a lot
    Thank You
    Known is a Drop, Unknown is an Ocean


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •