Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    25

    Unanswered: Full Outer Join Help please

    Hi All,

    I am new to my sql i need to apply Fullouterjoin between pos_no of below queires.Please help me how to apply

    Query 1:

    SELECT
    MIN(till_close.start_transaction_id) AS start_trans_id,
    MAX(till_close.end_transaction_id) AS end_trans_id,
    pos_config.pos_no,
    pos_config.name AS pos_name,
    SUM(transaction_tender.amount) AS EodDeposit
    FROM
    till_close
    LEFT OUTER JOIN employee ON (till_close.employee_id = employee.employee_id)
    INNER JOIN pos_config ON (till_close.pos_config_id = pos_config.pos_config_id)
    INNER JOIN transaction_tender ON (till_close.end_transaction_id = transaction_tender.transaction_id)
    INNER JOIN media_type ON (transaction_tender.media_type_id = media_type.media_type_id)
    WHERE
    DATE_FORMAT(till_close.transaction_date,'%d/%m/%Y') = DATE_FORMAT(STR_TO_DATE(?,'%d/%m/%Y'),'%d/%m/%Y')
    AND transaction_tender.media_type_id NOT IN (10000)
    GROUP BY
    pos_config.pos_no
    ORDER BY
    pos_config.pos_no



    Query:2


    SELECT
    pos_config.pos_no,
    pos_config.name AS pos_name,
    SUM(CASE WHEN transaction.transaction_type_id=7 AND ((SELECT COUNT(transaction_id) FROM transaction_tender WHERE transaction_id = transaction.transaction_id AND media_type_id IN (SELECT media_type_id FROM media_type WHERE tender_type_id=12) AND amount < 0 )>0) THEN 0
    WHEN transaction_tender.balance < 0
    THEN transaction_tender.amount
    ELSE (transaction_tender.amount - transaction_tender.balance)
    END) AS net
    FROM
    TRANSACTION
    INNER JOIN transaction_tender ON (transaction.transaction_id = transaction_tender.transaction_id)
    INNER JOIN media_type ON (transaction_tender.media_type_id = media_type.media_type_id)
    INNER JOIN pos_config ON (transaction.pos_config_id = pos_config.pos_config_id)
    WHERE
    transaction.transaction_date = DATE_FORMAT(STR_TO_DATE(?, '%d/%m/%Y'), '%Y-%m-%d')
    AND transaction.transaction_type_id IN (1,5,7)
    AND transaction.transaction_status_id = 3
    AND transaction.is_training_mode = 0
    GROUP BY
    pos_config.pos_no

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Just an idea.

    Code:
    /*
    Replace <<<Query:1>>> and <<<Query:2>>> by your Query:1 and Query:2.
    */
    SELECT q1.pos_no
         , q1.pos_name
         , q1.start_trans_id
         , q1.end_trans_id
         , q1.EodDeposit
         , q2.net
     FROM  ( <<<Query:1>>> ) AS q1
     LEFT  OUTER JOIN
           ( <<<Query:2>>> ) AS q2
      ON   q2.pos_no = q1.pos_no
    UNION
    SELECT q2.pos_no
         , q2.pos_name
         , q1.start_trans_id
         , q1.end_trans_id
         , q1.EodDeposit
         , q2.net
     FROM  ( <<<Query:1>>> ) AS q1
     RIGHT OUTER JOIN
           ( <<<Query:2>>> ) AS q2
      ON   q1.pos_no = q2.pos_no
    ;

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have a mini tutorial which explains this MySQL – FULL OUTER JOIN IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Mar 2013
    Posts
    25
    Hi Tonkuma,

    Thank you but i am getting some null values also.How to remove those null values.
    Please suggest

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by divyavani View Post
    Hi Tonkuma,

    Thank you but i am getting some null values also.How to remove those null values.
    Please suggest
    The NULL values are the point of using a FULL join. Use an INNER JOIN to remove those NULL values.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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