MSSQL full outer join wrong output

Question

I am a newbie in MSSQL. I keep Tax Payers and Sale and Purchase invoices of them.

I want both purchase and sale Invoices together of a specific Tax Payer in the same row. I tried to use full outer join in a stored procedure to retrieve the Invoices by day. But it does not work as I want. This is what I've done so far:

CREATE PROC InvoiceByDay @InvoiceDate datetime AS
SELECT s.Tax_Payer_ID AS ID, 
  s.Sale_InvoiceID AS [Sale Invoice Number], 
  s.Sale_Total_Amount AS [Total Sale Amount], 
  p.Purchase_InvoiceID AS [Purchase Invoice Number], 
  p.Purchase_Total_Amount AS [Total Purchase Amount]
FROM Sale_Invoice s 
FULL OUTER JOIN Purchase_Invoice p
  ON s.Sale_Date=@InvoiceDate 
  and s.Tax_Payer_ID=p.Tax_Payer_ID 
  and s.Sale_Date=p.Purchase_Date 

And this is the output:

Even the second row is not in the specificated day, it is still on the list.What could be a good solution?Any help will be highly appreciated.

Answer 1

A full outer join keeps all records from both tables, even when there is no matching key. I think you want to do the filtering before the join:

SELECT s.tax_payer_id          AS ID, 
       s.sale_invoiceid        AS [Sale Invoice Number], 
       s.sale_total_amount     AS [Total Sale Amount], 
       p.purchase_invoiceid    AS [Purchase Invoice Number], 
       p.purchase_total_amount AS [Total Purchase Amount] 
FROM (SELECT s.* 
      FROM sale_invoice s 
      WHERE s.sale_date = @InvoiceDate
     ) s FULL OUTER JOIN
     (SELECT p.* 
      FROM purchase_invoice p 
      WHERE p.purchase_date = @InvoiceDate
     ) p 
     ON s.tax_payer_id = p.tax_payer_id; 

This should return all purchase and sales records on that date -- and only on that date -- even when there are no matches.

Answer 2

I believe you want the s.Sale_Date=@InvoiceDate in the WHERE clause, not the ON clause of the join. Also, since you are using a full join, you will need both sides of this condition as well so you need to add OR p.Purchase_Date = @InvoiceDate or you will not get any rows when there is no match in the left table (since s.Sale_Date will be NULL):

SELECT s.Tax_Payer_ID AS ID, 
  s.Sale_InvoiceID AS [Sale Invoice Number], 
  s.Sale_Total_Amount AS [Total Sale Amount], 
  p.Purchase_InvoiceID AS [Purchase Invoice Number], 
  p.Purchase_Total_Amount AS [Total Purchase Amount]
FROM Sale_Invoice s 
FULL OUTER JOIN Purchase_Invoice p
  ON s.Tax_Payer_ID=p.Tax_Payer_ID 
  and s.Sale_Date=p.Purchase_Date 
WHERE s.Sale_Date = @InvoiceDate 
OR p.Purchase_Date = @InvoiceDate

Answer 3

The first part of your ON clause (i.e. s.Sale_Date=@InvoiceDate) should actually be in a where clause. You want to do something like:

FULL OUTER JOIN Purchase_Invoice p
  and s.Tax_Payer_ID=p.Tax_Payer_ID 
  and s.Sale_Date=p.Purchase_Date
WHERE s.Sale_Date=@InvoiceDate

Also, note that FULL OUTER JOIN will give you results for rows that don't have matching keys. If you only want records that had the same tax payer id and sale date in both tables, consider using INNER JOIN instead.