0

I have two Tables that I would like to run exception on.

Table 1 'Retail'

Columns in Retail is as follows.

Branch_Name, Customer_Name, Document_Number

Table 2 'Doc'

Branch_Name, Customer_Name, Invoice_Number

Now document number and Invoice number is the same thing.

what I want the query to show me is, how many records do we not have in Doc that is in retail.

Example. 'Retail'

Branch_Name, Customer_Name, Document_Number
sat             BOSS           s11121
bat             Kiven          s25456

Table 2 'Doc'

Branch_Name, Customer_Name, Invoice_Number
sat             BOSS           s11121

Result

We are missing, 

Branch_Name, Customer_Name, Invoice_Number
bat            Kiven         s25456

thank you

user3906930
  • 67
  • 2
  • 10

3 Answers3

1

The following will produce all rows from Retail and only those record that don't have a corresponding/identical row in Doc.

select *
from Retail r
  left outer join Doc d on r.Branch_Name = d.Branch_Name
    and r.Customer_Name = d.Customer_Name
    and r.Document_Number = d.Invoice_Number
where d.Branch_Name is null;
Michael Y.
  • 661
  • 7
  • 12
  • 1
    I suggest you add some explanation to your answer. Code-only answers are sometimes good enough, but code+explanation answers are always better – Barranka Jan 26 '15 at 18:13
  • The question/request specifically asked for a query. I'll add a note to explain though... – Michael Y. Jan 27 '15 at 19:46
0

Try this:

 select retail.* from doc where Retail left join doc on Document_number =Invoice_Number where Document_number is null
Jens
  • 67,715
  • 15
  • 98
  • 113
  • No offence, as far as I know IN operator is faster when unique id is selected. Please have a look at http://stackoverflow.com/questions/1200295/sql-join-vs-in-performance and http://blog.sqlauthority.com/2008/04/22/sql-server-better-performance-left-join-or-not-in/ – Anand Gangadhara Jan 26 '15 at 14:21
0

Try joining the two table using left join and pick up the columns which are null in Doc table like:

SELECT *
FROM Retail r LEFT JOIN DOC d
ON   r.Branch_Name = d.Branch_Name
AND  r.Document_Number = d.Invoice_Number
WHERE d.Invoice_Number IS NULL
SMA
  • 36,381
  • 8
  • 49
  • 73