Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: SQL join problem

    I have two table:
    Purchase:
    customer_id purchase_value
    1 200
    2 300
    3 400

    Sale:
    customer_id Sale_value
    1 200
    2 100
    5 500
    7 100

    I want to select data from the above two table in the following format
    customer_id purchase_value Sale_value
    1 200 200
    2 300 100
    3 400 0
    5 0 500
    7 0 100

    can any body help me??
    thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Code:
    SELECT CASE
             WHEN p.customer_id IS NULL THEN s.customer_id
             WHEN s.customer_id IS NULL THEN p.customer_id
             ELSE p.customer_id
           END AS customer_id,
           CASE
             WHEN p.purchase_value IS NULL THEN 0
             ELSE p.purchase_value
           END AS purchase_value,
           CASE
             WHEN s.sale_value IS NULL THEN 0
             ELSE s.sale_value
           END AS sale_value
    FROM purchase p 
      FULL OUTER JOIN sales s ON s.customer_id = p.customer_id;

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You can replace
    Code:
    CASE
             WHEN p.purchase_value IS NULL THEN 0
             ELSE p.purchase_value
           END
    with COALESCE(p.purchase_value, 0).

    I assume there will be a Customer table somewhere. So the query could be further simplified to
    Code:
    SELECT C.ID as customer_id,
           COALESCE(p.purchase_value, 0) AS purchase_value,
           COALESCE(s.sale_value, 0) AS sale_value
    FROM Customer as C
    	LEFT OUTER JOIN Purchase AS P ON
    		 C.Id = p.customer_id
    	LEFT OUTER JOIN Sales AS S ON 
    		C.Id = s.customer_id
    WHERE COALESCE(p.customer_id, s.customer_id) IS NOT NULL
    "WHERE COALESCE(p.customer_id, s.customer_id) IS NOT NULL", will filter out Customers who don't appear in Purchase and don't appear in Sales.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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