-2

I have the following:

TableA

ID | DocumentType | DocumentCode | DocumentDate | Warehouse | RefecenceCode  
---+--------------+--------------+--------------+-----------+--------------    
 1 | DeliveryNote | DOC-001      | 2017-04-21   |    1      |     NULL   
 2 | Invoice      | DOC-002      | 2017-04-21   |    2      |    DOC-001

As you can see, the warehouse is different on each document and DOC-002 is related to DOC-001 through the information in ReferenceCode column (which means that was created starting from DOC-001 as a source document).

It is supposed for the DOC-002 to have the same information but sometimes might be different and in this case, I was tried to create a query (I think self join applies here) in order to check what information is different in the DOC-002 in this case compared to DOC-001, based on the reference code, but I couldn't managed to do it.

If someone could give me a hand, I'll be very grateful.

This is the SQL query:

select * 
from TableA tbl
inner join TableA tbla on tbl.id = tbla.id
where tbla.ReferenceCode = tbl.DocumentCode
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cdrrr
  • 1,138
  • 4
  • 13
  • 44
  • 1
    What error did you get? Please show the code you tried. – Tab Alleman Apr 21 '17 at 13:00
  • 1
    Please add a bit more source data and the desired result. – PM 77-1 Apr 21 '17 at 13:02
  • @TabAlleman - I have edited the post. I'm not getting any error, it just i don't get the result, no results returned. – cdrrr Apr 21 '17 at 13:13
  • You aren't getting any results because you're linking on ID, and 1 doesn't ever equal 2. Your first step is probably to join on `tbl.DocumentCode = tbla.ReferenceCode`, but I'm still not sure what you want the results to look like. – Jerrad Apr 21 '17 at 13:20
  • @Jerrad - i would like to display only the line that has as a reference code another document and if contains different information compared to the source document. – cdrrr Apr 21 '17 at 14:17
  • What does "have the same information but sometimes might be different" mean? PS Please edit clarifications into your question, don't put them in comments. Please read and act on [ask] and [mcve]. – philipxy Apr 26 '17 at 06:03

1 Answers1

0

You indeed want to join the table to itself. But joining on the ID column won't work, because that column doesn't relate records to each other. Instead, you need to join on the DocumentCode and ReferenceCode fields. Then only include the records that have some difference (in this case, I'm only comparing the DocumentDate and Warehouse fields).

select tbla.* 
from TableA tbl
join TableA tbla on tbl.DocumentCode = tbla.ReferenceCode
where tbla.DocumentDate != tbl.DocumentDate
or tbla.Warehouse != tbl.Warehouse
Jerrad
  • 5,240
  • 1
  • 18
  • 23
  • It shouldn't take into consideration the DocumentType - this information is pure informative. It has to compare and if there are any differences between the source document and the destination document, it should display that line. – cdrrr Apr 21 '17 at 14:43
  • Just include whichever fields you *do* want to consider in the `WHERE` clause. – Jerrad Apr 21 '17 at 14:47
  • I would like to display only the row linked to another one through the `ReferenceCode`. I couldn't managed to achieve it... – cdrrr Apr 22 '17 at 13:01
  • 1
    Edit your question to include an example of what you want the results to be. – Jerrad Apr 24 '17 at 09:23