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

glennyboy 121 Reputation points
2023-05-25T05:26:56.4966667+00:00

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

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,298 questions
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,646 Reputation points
    2023-05-26T02:13:02.82+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. LiHongMSFT-4306 25,646 Reputation points
    2023-05-25T07:50:49.5266667+00:00

    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.

    0 comments No comments

  2. glennyboy 121 Reputation points
    2023-05-25T22:49:08.7633333+00:00

    Good day!

    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!

    0 comments No comments