MS SQL Query - JOIN multiple tables but it duplicates results;

Question

MS SQL Query - JOIN multiple tables but it duplicates results;

glennyboy 121

Good day!

it duplicates results when I run this query below;

MS SQl Query - Duplicate results

if II comment out "LEFT JOIN BBI$Barcodes AS BAR", the query results are correct.

MS SQL Query - Results

Thank you!

Viorel 122.5K Reputation points

2023-05-25T07:45:14.7133333+00:00

The rows do not seem duplicated.

What result do you expect when LEFT JOIN BBI$Barcodes is included?
glennyboy 121 Reputation points

2023-05-25T22:50:25.14+00:00

thank you for your reply

i would like to include the barcode no column in Item Ledger Entry table

Accepted answer

2 additional answers

Your answer

Viorel 122.5K Reputation points

2023-05-25T07:45:14.7133333+00:00

The rows do not seem duplicated.

What result do you expect when LEFT JOIN BBI$Barcodes is included?
glennyboy 121 Reputation points

2023-05-25T22:50:25.14+00:00

thank you for your reply

i would like to include the barcode no column in Item Ledger Entry table

Answer 1

Hi @glennyboy

As I answered above, it's not duplicate records. Didn't you realize that there are two different Barcode No for each Item No and Variant Code.

For example, let's focus one record: Entry No_ = 3485739.

User's image

At the same time, in the BAR Table, there are two different Barcode No with same [Item No_] and [Variant Code].

User's image

Therefore, when you use this record left join BAR Table on [Item No_] and [Variant Code], you will get two rows like this:

User's image

In conclusion, if you want to display only one row for each Entry No, you need first ensure that there is only one Barcode No for each [Item No_] and [Variant Code].

Best regards,

Cosmog Hong

Answer 2

Hi @glennyboy

JOIN multiple tables but it duplicates results

Actually, its not duplicate records.

User's image

There are two different BarcodeNo in your left join table set. That's why you got two records for each EntryNo.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Good day!

it duplicates results when I run this query below;

SELECT
ILE.[Entry No_]
,ILE.[Location Code]
,BAR.[Barcode No_] AS [Barcode No]
,ILE.[Item No_] AS [Item No]
,ILE.[Variant Code]
,IT.Description AS Description
,ILE.Quantity AS Quantity

FROM [BBI$Item Ledger Entry] AS ILE

LEFT JOIN [BBI$Item Variant] AS IV
ON ILE.[Item No_] =IV.[Item No_] AND ILE.[Variant Code]=IV.Code

LEFT JOIN BBI$Item AS IT
ON ILE.[Item No_]= IT.No_

LEFT JOIN BBI$Barcodes AS BAR
ON ILE.[Item No_] = BAR.[Item No_] AND ILE.[Variant Code] = BAR.[Variant Code] 

LEFT JOIN BBI$Store AS ST
ON ILE.[Location Code] = ST.[Location Code] 

WHERE	
ILE.[Location Code] = 'HPCB' AND
ILE.[Item No_] = 'HPW8379-1-01' AND
ILE.[Posting Date] <= '12/31/2099'

ORDER BY 
IT.Description
,ILE.[Item No_]
,ILE.[Variant Code]

Query Results;

Total Quantity: 30

Entry No_	Location Code		Barcode No		Item No		Variant Code		Description		Quantity
3485739		HPCB		HPWATASHATCWBLK10		HPW8379-1-01		10		ATASHA TOE CAP W		-1
3485739		HPCB		HPWATASHATOCPBLK10		HPW8379-1-01		10		ATASHA TOE CAP W		-1
3414667		HPCB		HPWATASHATCWBLK10		HPW8379-1-01		10		ATASHA TOE CAP W		1
3414667		HPCB		HPWATASHATOCPBLK10		HPW8379-1-01		10		ATASHA TOE CAP W		1
4048905		HPCB		HPWATASHATCWBLK10		HPW8379-1-01		10		ATASHA TOE CAP W		2
4048905		HPCB		HPWATASHATOCPBLK10		HPW8379-1-01		10		ATASHA TOE CAP W		2
3414669		HPCB		HPWATASHATCWBLK50		HPW8379-1-01		50		ATASHA TOE CAP W		1
3414669		HPCB		HPWATASHATOCPBLK5		HPW8379-1-01		50		ATASHA TOE CAP W		1
4048907		HPCB		HPWATASHATCWBLK50		HPW8379-1-01		50		ATASHA TOE CAP W		2
4048907		HPCB		HPWATASHATOCPBLK5		HPW8379-1-01		50		ATASHA TOE CAP W		2
3476914		HPCB		HPWATASHATCWBLK50		HPW8379-1-01		50		ATASHA TOE CAP W		-1
3476914		HPCB		HPWATASHATOCPBLK5		HPW8379-1-01		50		ATASHA TOE CAP W		-1
3493224		HPCB		HPWATASHATCWBLK60		HPW8379-1-01		60		ATASHA TOE CAP W		-1
3493224		HPCB		HPWATASHATOCPBLK6		HPW8379-1-01		60		ATASHA TOE CAP W		-1
3494762		HPCB		HPWATASHATCWBLK60		HPW8379-1-01		60		ATASHA TOE CAP W		-1
3494762		HPCB		HPWATASHATOCPBLK6		HPW8379-1-01		60		ATASHA TOE CAP W		-1
4048909		HPCB		HPWATASHATCWBLK60		HPW8379-1-01		60		ATASHA TOE CAP W		2
4048909		HPCB		HPWATASHATOCPBLK6		HPW8379-1-01		60		ATASHA TOE CAP W		2
3414671		HPCB		HPWATASHATCWBLK60		HPW8379-1-01		60		ATASHA TOE CAP W		2
3414671		HPCB		HPWATASHATOCPBLK6		HPW8379-1-01		60		ATASHA TOE CAP W		2
3414673		HPCB		HPWATASHATCWBLK70		HPW8379-1-01		70		ATASHA TOE CAP W		2
3414673		HPCB		HPWATASHATOCPBLK7		HPW8379-1-01		70		ATASHA TOE CAP W		2
4048911		HPCB		HPWATASHATCWBLK70		HPW8379-1-01		70		ATASHA TOE CAP W		4
4048911		HPCB		HPWATASHATOCPBLK7		HPW8379-1-01		70		ATASHA TOE CAP W		4
3476915		HPCB		HPWATASHATCWBLK70		HPW8379-1-01		70		ATASHA TOE CAP W		-1
3476915		HPCB		HPWATASHATOCPBLK7		HPW8379-1-01		70		ATASHA TOE CAP W		-1
3492510		HPCB		HPWATASHATCWBLK70		HPW8379-1-01		70		ATASHA TOE CAP W		-1
3492510		HPCB		HPWATASHATOCPBLK7		HPW8379-1-01		70		ATASHA TOE CAP W		-1
3482709		HPCB		HPWATASHATCWBLK80		HPW8379-1-01		80		ATASHA TOE CAP W		-1
3482709		HPCB		HPWATASHATOCPBLK8		HPW8379-1-01		80		ATASHA TOE CAP W		-1
3487869		HPCB		HPWATASHATCWBLK80		HPW8379-1-01		80		ATASHA TOE CAP W		-1
3487869		HPCB		HPWATASHATOCPBLK8		HPW8379-1-01		80		ATASHA TOE CAP W		-1
4048913		HPCB		HPWATASHATCWBLK80		HPW8379-1-01		80		ATASHA TOE CAP W		3
4048913		HPCB		HPWATASHATOCPBLK8		HPW8379-1-01		80		ATASHA TOE CAP W		3
3414675		HPCB		HPWATASHATCWBLK80		HPW8379-1-01		80		ATASHA TOE CAP W		2
3414675		HPCB		HPWATASHATOCPBLK8		HPW8379-1-01		80		ATASHA TOE CAP W		2
3414677		HPCB		HPWATASHATCWBLK90		HPW8379-1-01		90		ATASHA TOE CAP W		2
3414677		HPCB		HPWATASHATOCPBLK9		HPW8379-1-01		90		ATASHA TOE CAP W		2
4048915		HPCB		HPWATASHATCWBLK90		HPW8379-1-01		90		ATASHA TOE CAP W		3
4048915		HPCB		HPWATASHATOCPBLK9		HPW8379-1-01		90		ATASHA TOE CAP W		3
3475415		HPCB		HPWATASHATCWBLK90		HPW8379-1-01		90		ATASHA TOE CAP W		-1
3475415		HPCB		HPWATASHATOCPBLK9		HPW8379-1-01		90		ATASHA TOE CAP W		-1
3476127		HPCB		HPWATASHATCWBLK90		HPW8379-1-01		90		ATASHA TOE CAP W		-1
3476127		HPCB		HPWATASHATOCPBLK9		HPW8379-1-01		90		ATASHA TOE CAP W		-1
4090260		HPCB		HPWATASHATCWBLK90		HPW8379-1-01		90		ATASHA TOE CAP W		-1
4090260		HPCB		HPWATASHATOCPBLK9		HPW8379-1-01		90		ATASHA TOE CAP W		-1

if I remove "LEFT JOIN BBI$Barcodes AS BAR", the query results are correct.

SELECT
ILE.[Entry No_]
,ILE.[Location Code]
,ILE.[Item No_] AS [Item No]
,ILE.[Variant Code]
,IT.Description AS Description
,ILE.Quantity AS Quantity
FROM [BBI$Item Ledger Entry] AS ILE
LEFT JOIN [BBI$Item Variant] AS IV
ON ILE.[Item No_] =IV.[Item No_] AND ILE.[Variant Code]=IV.Code
LEFT JOIN BBI$Item AS IT
ON ILE.[Item No_]= IT.No_
LEFT JOIN BBI$Store AS ST
ON ILE.[Location Code] = ST.[Location Code] 
WHERE	
ILE.[Location Code] = 'HPCB' AND
ILE.[Item No_] = 'HPW8379-1-01' AND
ILE.[Posting Date] <= '12/31/2099'
ORDER BY 
IT.Description
,ILE.[Item No_]
,ILE.[Variant Code]

Query Results

Total Quantity: 15

Entry No_		Location Code		Item No		Variant Code		Description		Quantity
3485739		           HPCB		HPW8379-1-01		10		ATASHA TOE CAP W		-1
3414667		           HPCB		HPW8379-1-01		10		ATASHA TOE CAP W		1
4048905		           HPCB		HPW8379-1-01		10		ATASHA TOE CAP W		2
3414669		           HPCB		HPW8379-1-01		50		ATASHA TOE CAP W		1
4048907		           HPCB		HPW8379-1-01		50		ATASHA TOE CAP W		2
3476914		           HPCB		HPW8379-1-01		50		ATASHA TOE CAP W		-1
3493224		           HPCB		HPW8379-1-01		60		ATASHA TOE CAP W		-1
3494762		           HPCB		HPW8379-1-01		60		ATASHA TOE CAP W		-1
4048909		           HPCB		HPW8379-1-01		60		ATASHA TOE CAP W		2
3414671		           HPCB		HPW8379-1-01		60		ATASHA TOE CAP W		2
3414673		           HPCB		HPW8379-1-01		70		ATASHA TOE CAP W		2
4048911		           HPCB		HPW8379-1-01		70		ATASHA TOE CAP W		4
3476915		           HPCB		HPW8379-1-01		70		ATASHA TOE CAP W		-1
3492510		           HPCB		HPW8379-1-01		70		ATASHA TOE CAP W		-1
3482709		           HPCB		HPW8379-1-01		80		ATASHA TOE CAP W		-1
3487869		           HPCB		HPW8379-1-01		80		ATASHA TOE CAP W		-1
4048913		           HPCB		HPW8379-1-01		80		ATASHA TOE CAP W		3
3414675		           HPCB		HPW8379-1-01		80		ATASHA TOE CAP W		2
3414677		           HPCB		HPW8379-1-01		90		ATASHA TOE CAP W		2
4048915		           HPCB		HPW8379-1-01		90		ATASHA TOE CAP W		3
3475415		           HPCB		HPW8379-1-01		90		ATASHA TOE CAP W		-1
3476127		           HPCB		HPW8379-1-01		90		ATASHA TOE CAP W		-1
4090260		           HPCB		HPW8379-1-01		90		ATASHA TOE CAP W		-1

Item Ledger Entry Table

Entry No_		Location Code		Item No_		Variant Code		Quantity
3485739			    HPCB		HPW8379-1-01		      10		-1
3476914		        HPCB		HPW8379-1-01		      50		-1
3493224		        HPCB		HPW8379-1-01		      60		-1
3494762		        HPCB		HPW8379-1-01		      60		-1
3476915		        HPCB		HPW8379-1-01		      70		-1
3492510		        HPCB		HPW8379-1-01		      70		-1
3482709		        HPCB		HPW8379-1-01		      80		-1
3487869		        HPCB		HPW8379-1-01		      80		-1
3475415		        HPCB		HPW8379-1-01		      90		-1
3476127		        HPCB		HPW8379-1-01		      90		-1
4090260		        HPCB		HPW8379-1-01		      90		-1
3414667		        HPCB		HPW8379-1-01		      10		1
4048905		        HPCB		HPW8379-1-01		      10		2
3414669		        HPCB		HPW8379-1-01		      50		1
3414671		        HPCB		HPW8379-1-01		      60		2
3414673		        HPCB		HPW8379-1-01		      70		2
3414675		        HPCB		HPW8379-1-01		      80		2
3414677		        HPCB		HPW8379-1-01		      90		2
4048907		        HPCB		HPW8379-1-01		      50		2
4048909		        HPCB		HPW8379-1-01		      60		2
4048911		        HPCB		HPW8379-1-01	 	      70		4
4048913		        HPCB		HPW8379-1-01		      80		3
4048915		        HPCB		HPW8379-1-01		      90		3

BAR Table

Barcode No_          Item No_                  Description	                Variant Code
HPWATASHATCWBLK10	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		10
HPWATASHATOCPBLK10	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		10
HPWATASHATOCPBLK11	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		11
HPWATASHATOCPBLK4	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		4
HPWATASHATCWBLK50	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		50
HPWATASHATOCPBLK5	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		50
HPWATASHATCWBLK60	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		60
HPWATASHATOCPBLK6	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		60
HPWATASHATCWBLK70	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		70
HPWATASHATOCPBLK7	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		70
HPWATASHATCWBLK80	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		80
HPWATASHATOCPBLK8	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		80
HPWATASHATCWBLK90	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		90
HPWATASHATOCPBLK9	HPW8379-1-01		ATASHA TOE CAP W - BLACK LEATHER		90

Variant Table

Item No_		      Description		Description 2		Code
HPW8379-1-01		ATASHA TOE CAP W	BLACK LEATHER		10
HPW8379-1-01		ATASHA TOE CAP W	BLACK LEATHER		11
HPW8379-1-01		ATASHA TOE CAP W	BLACK LEATHER		4
HPW8379-1-01		ATASHA TOE CAP W	BLACK LEATHER		50
HPW8379-1-01		ATASHA TOE CAP W	BLACK LEATHER		60
HPW8379-1-01		ATASHA TOE CAP W	BLACK LEATHER		70
HPW8379-1-01		ATASHA TOE CAP W	BLACK LEATHER		80
HPW8379-1-01		ATASHA TOE CAP W	BLACK LEATHER		90

I would like to include the Barcode No column (BAR Table) in the Item Ledger Table.

Thank you!

Share via

MS SQL Query - JOIN multiple tables but it duplicates results;

2 additional answers

Your answer