Using FULL OUTER JOIN does not return me all lines SQL

Databases
,
1

Hello!
I have 2 tables document_line where planned work lines are saved and document_line_entry where entered work lines are saved.
I do not know why but i cant get all lines back (expecting 5)
Any ideas whats wrong?

Data in table document_line:

line_no  |  bin  | document_no
1        |  BIN1 | DOC1
2        |  BIN2 | DOC1
3        |  BIN3 | DOC1

Data in table document_line_entry:

line_no  |  bin  | document_no
1        |  BIN4 | DOC1
1        |  BIN5 | DOC1

This result i get:

planned shelf  | shelf fact  
BIN1           | BIN4
BIN1           | BIN5
BIN2           | -
BIN3           | -

This result i expect:

planned shelf  | shelf fact  
BIN1           | -
BIN1           | BIN4
BIN1           | BIN5
BIN2           | -
BIN3           | -

Here is my SQL:

SELECT a.line_no, e.line_no, a.bin, e.bin
FROM [dbo].[document_line] as a 					
FULL OUTER JOIN [dbo].[document_line_entry] as e
ON e.[line_no]=a.[line_no]
WHERE a.[document_no]='DOC1'
2

Based on the data sample you provided, the result you are getting IS correct. line_no 1 from document_line matches the two records on document_line_entry, where line_nos 2 and 3 do not, so you get nulls in the values.

The only time you get a null row is when there are NO matches at all.

1 Like
3

@DaveMaxwell Hi, thanks for replay! How then i can get expected value with one query? Is that even possible?

4

Why would you want to have a bogus returned value?

5

Lets say i should receive pack with 3 different items in it BIN1, BIN2 and BIN3 (i show then in html table) but when i open
pack there is also BIN4 and BIN5

I need to enter into document_line_entry all received packs so its 5 and i want them to be show in same html table

6

I’m sorry. I just don’t understand. In the OP, you were talking about planned and entered work, now you’re talking what looks to be a pack of goods mailed to you that is distributed to different areas?

7

Well planned work is to receive 3 items entered work is 5 items

8

I’m sorry, but I just don’t understand. Your data structure doesn’t support what you’re looking to get out of it.

9

I… COULD be wrong, but what I think he’s trying to do here Dave could be solved as:

ON e.line_no = a.line_no AND e.bin = a.bin

It wouldnt quite give him the result he expects, but it would be the right number of rows.

(Essentially what i think he’s looking for is “Find where things didnt end up in the right place.”… so any row where planned_shelf is Null is a misplaced item, and any row where shelf_fact is Null is a line item that is missing…?)

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.