Title: SQL Join not returning the correct results for combined tables
Tags: sql-server
Question: I'm trying to insert the result of joining two tables together into another table I have but I am getting incorrect results.
Here is the code for the join:
Insert into USOR_DSOR(Sap_Code,Product_description,Pack,Location, USOR_quantity, DSOR_quantity)
select USOR.Sap_Code, USOR.Product_description , USOR.Pack, USOR.Location,  USOR.Stock, DSOR.Total
from USOR
left join DSOR
on USOR.Sap_Code = DSOR.Sap_code
group by USOR.Sap_code, USOR.Product_description, USOR.Pack, USOR.Location, USOR.Stock, DSOR.Total
This should give me the results of the two combined tables but instead it is giving me multiple duplicated values:
Here is a query I ran to show the error:
select * from USOR
where Sap_Code = 1018930
select * from dsor
where Sap_code = 1018930
select  * from USOR_DSOR
where Sap_code = 1018930
This is the result that I get:
As you can see from the image.
The first table has a total of 5 records for this product I am querying and the second table has a total of 4 records which are both correct but for some reason the combined table has 30 records for the same product.
I'm not sure if this has something to do with the join type I used. I have tried different joins and I am still getting the same incorrect result.
Any advice will be much appreciated.
**EDIT
I have been asked to provide more information on what I am trying to achieve so I will go into more detail.
I have a total of five tables tables as shown below:
create table EWM(
Date_loaded date,
Sap_code bigint,
Product_description varchar(100),
Location nvarchar(50),
Storage_type varchar(50),
Quantity int,
Sap_batch nvarchar(50),
Expiry_date date,
Stock_type varchar(50)
--Table for USOR Data--
create table USOR(
Date_Loaded date,
Sap_Code bigint,
Product_description varchar(100),
Pack nvarchar(50),
Cost_price decimal,
Trade_price decimal,
Stock int,
Location nvarchar(50),
--Table for DSOR--
create table DSOR(
Date_Loaded datetime,
Sap_code bigint,
Product_description varchar(100),
Bin_location nvarchar(50),
Location varchar(50),
Pack nvarchar(50),
Units int,
Total int,
--Table for Quantites for USOR and DSOR--
CREATE TABLE USOR_DSOR
Sap_code bigint,
Product_description varchar(100),
Pack nvarchar(50),
Location nvarchar(50),
USOR_quantity int,
DSOR_quantity int
--Table for difference between EWM and USOR+DSOR--
create table Misalignment
Sap_code bigint,
Product_description varchar(100),
Pack nvarchar(50),
Location nvarchar(50),
Total_difference int
The first table EWM contains information about the product data stored in a warehouse but the main column I am querying is the quantity column. This contains data of all the stock quantity of each item held warehouse.
The second table USOR also contains product data and same with the EWM table the column I am interested in the most is the Stock column which contains the information about the quantity. The difference between the EWM table and USOR table is that EWM includes Work in progress while USOR does not.
The third table is DSOR which contains information about the work in progress for each product.
I am building a stock synch and the calculation is USOR + DSOR = EWM.
Where there is a variance I want to display these variances in the misalignment table I have created.
The ultimate result I'm trying to achieve is to to be able to run a query on the Misalignment table that will give me the results of stock that have had 5 or more instances of a variance between the three tables.
Here is my code in inserting the data into the other two tables - Misalignment and USOR_DSOR:
Insert into USOR_DSOR(Sap_Code,Product_description,Pack,Location, USOR_quantity, DSOR_quantity)
select USOR.Sap_Code, USOR.Product_description , USOR.Pack, USOR.Location,  USOR.Stock, DSOR.Total
from USOR
left join DSOR
on USOR.Sap_Code = DSOR.Sap_code
group by USOR.Sap_code, USOR.Product_description, USOR.Pack, USOR.Location, USOR.Stock, DSOR.Total
--Inserting data in Misalingment using query from USOR, USOR_DSOR, EWM
insert into Misalignment(Sap_code, Product_description, Pack, Location, Total_difference)
Select USOR.Sap_Code, USOR.Product_description, USOR.Pack, USOR.Location, sum(EWM.Quantity - USOR_quantity+USOR_DSOR.DSOR_quantity) as Total_difference
from USOR
left join USOR_DSOR
on USOR.Sap_Code = USOR_DSOR.Sap_code
LEFT join EWM
on USOR.Sap_code = EWM.Sap_code
left  join
on USOR.Sap_Code = DSOR.Sap_code
group by USOR.Sap_Code, USOR.Product_description, USOR.Pack, USOR.Location
Finally here is the query I have to display the misalignments with a 4 or more instances of variances:
SELECT COUNT(Total_difference) AS DiffCount, Sap_code, Product_description
, Location, Total_difference
FROM Misalignment
WHERE Total_difference &gt; 0
GROUP BY Sap_code, Product_description, Location, Total_difference
HAVING COUNT(Total_difference) &gt; 4
I hope this has added more clarification.
Apologies for the long winded explanation.
Comment: Maybe you want to aggregate the values of DSOR.Total (e.g. SUM or MAX)? As your query is currently written, the result seems (technically) correct.

Comment: OK. You will need to tell us what you actually want to achieve (e.g., in the final table, you expect X rows, and each row will be about what?). Also telling us what the tables represent will help, including how they are linked. Secondly, you have 30 rows because you've run the insert command twice, each inserting 15 rows (5 x 3). It's not 5 x 4 because two rows in DSOR have the same total and are thus being grouped.

Comment: You're getting 15 rows from the join. (There are 3 distinct values in the right-hand total/quantity column. Grouping is collapsing the two that are the same. In fact `select distinct` would be more appropriate.) You probably ran the insert twice which would get you up to 30. There's no reason to use a left join against this data.

Comment: Re "I have tried different joins" - it is usually better to understand a) what you need and b) what tools (language elements, algorithms etc) you can/should use to get required result. Programming by trial and error is possible, but not in such cases.

Comment: @seanb, I have added more information. Apologies if the format is a bit off or if the explanation is long winded. Only being learning SQL a few weeks so still very new.

Comment: @StephanBauer, Hi, I have added more context. Hope you can assist me in where I am going wrong

Comment: SAP code isn't unique. If you have 5 rows from one table with SAP code x and 4 rows in another table with the same SAP code and just join purely on that then you will get `5 x 4 = 20` rows in the result as every row in one side joins with every row in the other side. Either you need to join in some way that relates the rows better or aggregate both sides and join the aggregated results



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 0, 'CommentCount': 7, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2020:00:10/16/20', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<sql-server>', 'Title': 'SQL Join not returning the correct results for combined tables', 'ViewCount': 56, 'id': 64390876, 'user_AboutMe': None, 'user_AccountId': 19682418.0, 'user_DisplayName': 'Nitamashu', 'user_Reputation': 1.0, 'user_Views': 14.0, 'user_id': 14409235.0}}