MS SQL Query - JOIN mutiple tables but it duplicates results
-
MS SQL Query - JOIN multiple tables but it duplicate results;
Good day!
- it duplicate results when i run this query below;
- if i comment out "LEFT JOIN BBI$Barcodes AS BAR", the query results are correct.
Thank you!
-
change your select to include all columns from the commented out join - and uncomment it out as well and order by variant code
then look at the results and see what other columns from barcodes you may need to use on your join so it returns only 1 row.
-
If it was not obvious, what frederico_fons is saying is that you have a one-to-many relationship between your BBI$Barcodes table and your BBI$Item_Ledger_Entry table and you are seeking to make it a one-to-one relationship if that is possible.
Also it is considered poor practice to have spaces within any variable name whether it be a Table Name, Column Name, Local Variable, etc... If you want I have written a T-SQL Standards Document (compiled from numerous places and back by years of experience) that I wrote for our team and I can share that with you if you (or anyone) are interested. Just shoot me an email request for it at djensen765@gmail.com
-
This was removed by the editor as SPAM
-
Good day!
it duplicate 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 -1if i comment out "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 -1Item Ledger 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 3BAR 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 90Variant 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 90I would like to include the Barcode No column in the BAR Table to Item Ledger
Thank you.
-
Okay while that is all nice to know it does not drill down to the issue that you have which will require you to examine the basic data and those basic tables to figure out how to convert a one-to-many relationship into a one-to-one relationship -- OR -- if that is even possible.
Sharing the results of the queries with us does nothing to help you solve your problem because the problem resides in understanding your data prior to querying it. So do you understand your data? If you do, then the simpliest answers is to convert your one-to-many relationship into a one-to-one relationship.
We cannot do that for you without fully and completely understanding what the data is that you are working with. This is a fundamental aspect of creating queries. You can try to query data you do not understand but this usually produces garbage results, unless you are extremely lucky. Data analytics and thus queries are all about understanding the data you are working with and what you wish to render to whomever is looking at the results.
I wish I could do more for you, but when working with complete absolute unknowns that is basically impossible to do. If you can better portray your data in a manner that helps us understand that data, then perhaps someone can help you query that data in a meaningful way. But you need those basic building blocks or the house of cards collapses before you even get started, let alone get an answer.
Again, I would seriously like to help you, but I am looking at an major unknown with no pointers as to where to go.
-
The combination of [Item No_], [Variant Code], in BBI$Barcodes, is obviously not unique so you have to find some way to make it unique. eg If BBI$Barcodes has a DateAdded column, something like:
LEFT JOIN
(
SELECT [Item No_], [Variant Code], [Barcode No_]
,ROW_NUMBER() OVER (PARTITION BY [Item No_], [Variant Code] ORDER BY DateAdded DESC) AS rn
FROM BBI$Barcodes
) BAR
ON ILE.[Item No_] = BAR.[Item No_]
AND ILE.[Variant Code] = BAR.[Variant Code]
AND BAR.rn = 1Please read the following before posting again:
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
-
Try changing the left join on "bar" to this:
LEFT JOIN (SELECT
[Item No_]
,[Barcode No_]
,[Variant Code]
FROM BBI$Barcodes
GROUP BY [Item No_]
,[Barcode No_]
,[Variant Code]) AS BAR
ON ILE.[Item No_] = BAR.[Item No_] AND ILE.[Variant Code] = BAR.[Variant Code]And, as others have pointed out, you have issues in this structure and data that cannot be fixed in a public forum. And you really need to know and understand the nature of this data, and the structures, in order to be able to provide proper and accurate results to your users.
You may be able to get the query to work, BUT IT IS PRODUCING THE CORRECT RESULTS? How do you know that you do not need these duplicate rows?
Is this a third party app? The structure looks as if they are trying to handle "options" in a catalog of items for a retail store.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply