logo
down
shadow

SQL Server : duplicate rows when joining tables and inserting new data?


SQL Server : duplicate rows when joining tables and inserting new data?

By : Jackie
Date : November 21 2020, 04:01 AM
I wish did fix the issue. I have two tables, one that records holidays requested per EmployeeID (tbl.HolidayRequest): , Simply use a view:
code :
CREATE VIEW vw_HoursTakenPerEmployee AS
    SELECT le.[EmployeeNo.], 
           COALESCE(SUM(hrf.[HoursRequested]), 0) AS HoursTaken
    FROM [dbo].[LotusEmployees] le LEFT JOIN
         [dbo].[HolidayRequestForm] hrf
          ON le.[EmployeeNo.] = hrf.EmployeeNo.]
    GROUP BYle.[EmployeeNo.];
TRUNCATE TABLE [dbo].[HoursTakenPerEmployee];

INSERT INTO [dbo].[HoursTakenPerEmployee] . . .


Share : facebook icon twitter icon
When joining tables duplicate data is being returned from SQL Server

When joining tables duplicate data is being returned from SQL Server


By : Yeshwanth Reddy
Date : March 29 2020, 07:55 AM
To fix this issue I have a table like this:
code :
SELECT  em.Ename
FROM    dbo.Email_tbl e
        JOIN dbo.EmployeeMaster_tbl em ON em.Ecode = e.ECode
WHERE e.plcid = 25 
Joining two tables having duplicate data in both columns on the base of which we are joining

Joining two tables having duplicate data in both columns on the base of which we are joining


By : MAaxim
Date : March 29 2020, 07:55 AM
seems to work fine I have two tables. A column named CardName is in first table. There is duplicate data in this columns. That column also exists in second table. There is a column named amount related to each cardName also in second table. What i want is to select distinct CardName from 1st table and and take sum of all the amounts from second column whose cardname is in first table. BUT first table cardname should be distinct. what should i do?
code :
select name,sum(amount) from tableB 
where name in (select distinct name from TableA) 
group by name
with cte (name) as 
(
select distict name from TableA 

)
select cte.name,ISNULL(sum(count),0) from TableB as B 
left join cte.name = B.name 
Duplicate rows when joining three tables

Duplicate rows when joining three tables


By : Jonatán Concha
Date : March 29 2020, 07:55 AM
Does that help What is happening is that joining Report (1 row) to ClothingObservation (10 rows) produces 10 row (1 x 10), you then join to HygieneObservation (10 rows) which gives you 100. The reason this is happening is because after the initial join you have 10 rows with the same ReportID so the next join takes each of these 10 rows and joins to the 10 rows in HygieneObservation.
The solution for "20 rows with NULL values":
code :
SELECT 
    Report.ReportId, 
    Report.Period, 
    Report.Reporter, 
    Report.DepartmentId, 
    ClothingObservation.ClothingObservationId,
     NULL AS HygieneObservationId
FROM Report
LEFT JOIN ClothingObservation ON
    (ClothingObservation.ReportId = Report.ReportId)
UNION ALL
SELECT 
    Report.ReportId, 
    Report.Period, 
    Report.Reporter, 
    Report.DepartmentId, 
    NULL AS ClothingObservationId,
    HygieneObservation.HygieneObservationId
FROM Report
LEFT JOIN HygieneObservation ON
    (HygieneObservation.ReportId = Report.ReportId)
SELECT 
    Report.ReportId, 
    Report.Period, 
    Report.Reporter, 
    Report.DepartmentId, 

    MergedObservations.ClothingObservationId,
    MergedObservations.HygieneObservationId
FROM Report
    LEFT JOIN 
            ( SELECT COALESCE( ClothingObservation.ReportID, HygieneObservation.ReportID ) AS ReportID,
                    HygieneObservationID, ClothingObservationID -- Add appropriate columns
            FROM
                        ( SELECT ROW_NUMBER() OVER( PARTITION BY ReportID ORDER BY ClothingObservationID ) AS ResultID, ReportID, ClothingObservationID
                        FROM ClothingObservation ) AS ClothingObservation
               FULL OUTER JOIN
                        ( SELECT ROW_NUMBER() OVER( PARTITION BY ReportID ORDER BY HygieneObservationID  ) AS ResultID, ReportID, HygieneObservationID
                        FROM HygieneObservation ) AS HygieneObservation
                    ON ClothingObservation.ReportID = HygieneObservation.ReportID
                        AND ClothingObservation.ResultID = HygieneObservation.ResultID
            ) AS MergedObservations
        ON Report.ReportID = MergedObservations.ReportID
CREATE TABLE Report( ReportId INT, Period DATETIME, Reporter VARCHAR( 20 ), DepartmentId INT )
CREATE TABLE ClothingObservation( ClothingObservationID INT, ReportId INT )
CREATE TABLE HygieneObservation( HygieneObservationID INT, ReportId INT )

INSERT INTO Report
VALUES( 1, '2016-05-01', 'username', 1 )

INSERT INTO ClothingObservation
VALUES
( 1, 1 ), ( 2, 1 ), ( 3, 1 ), ( 4, 1 ), ( 5, 1 ), ( 6, 1 ), ( 7, 1 ), ( 8, 1 ), ( 9, 1 ), ( 10, 1 )

INSERT INTO HygieneObservation
VALUES
( 3, 1 ), ( 4, 1 ), ( 5, 1 ), ( 6, 1 ), ( 7, 1 ), ( 8, 1 ), ( 9, 1 ), ( 10, 1 ), ( 11, 1 ), ( 12, 1 ), ( 13, 1 )
Duplicate rows when joining tables

Duplicate rows when joining tables


By : Allen Koo
Date : March 29 2020, 07:55 AM
will be helpful for those in need I am not sure if desired output really has sense. But if it is what you really, REALLY need then.
code :
select coalesce(t.listid, c.listid, a.listid, h.listid) listid, 
       cladescription, headescription, actdescription
  from tblcgg t
  FULL OUTER join (select a.*, row_number() over(partition by listid order by cladescription) seq_no from tblcla a) c on t.listid=c.listid
  FULL OUTER join (select a.*, row_number() over(partition by listid order by actdescription) seq_no from tblact a) a on t.listid=a.listid and a.seq_no=c.seq_no
  FULL OUTER join (select a.*, row_number() over(partition by listid order by headescription) seq_no from tblhea a) h on h.listid=a.listid and (h.seq_no=c.seq_no or h.seq_no=a.seq_no) 
 where coalesce(t.listid, c.listid, a.listid, h.listid)=132623
select listid, 'cgg' source,description from tblcgg where listid=132623
UNION ALL
select listid, 'act' source,actdescription from tblact where listid=132623
UNION ALL
select listid, 'head' source,headescription from tblhea where listid=132623
UNION ALL
select listid, 'cla' source,cladescription from tblcla where listid=132623
Duplicate Rows when self joining tables in SQL

Duplicate Rows when self joining tables in SQL


By : user2118329
Date : March 29 2020, 07:55 AM
Hope that helps You may alter your join condition to check that the first part number is strictly less than the second one:
code :
SELECT
    t1.PARTNUMB, t1.PARTDESC, t1.ITEMCLSS, t2.PARTNUMB, t2.PARTDESC, t2.ITEMCLSS
FROM PARTFIRST t1
INNER JOIN PARTSECOND t2
    ON t1.WRHSNUMB = t2.WRHSNUMB AND
       t1.ITEMCLSS = t2.ITEMCLSS AND
       t1.PARTNUMB < t2.PARTNUMB;
Related Posts Related Posts :
  • Solving a Collation Problem between Two Tables
  • SQL Sub-Query from 2 Access Queries
  • Why CAST from Date time to varchar works in one Environment but not the other?
  • Power BI Matrix sum totalling %ages incorrectly
  • How to compare Hexadecimal values in oracle SQL
  • ACCESS/SQL - Create query that combines all unique records from multiple tables
  • Find repeated elements in a having count
  • Insert rows not contained in one table to another
  • Is it possible to add values from an array of tuples to separate columns in a table
  • Syntax error while using multiple rename RENAME expressions postgresql
  • SQL Server : concat values
  • Querying Column Headers in GBQ
  • How to sort the row of record and update dynamically in SQL?
  • Linked server not showing on database list
  • How do I dynamically make calculations via a CASE statement based on the results of the previous row's calculations in O
  • SQL Trouble finding SUM Costs of Object grouped by the object on different dates
  • oracle query to fetch count
  • Epoch conversion to DateTime in SQL
  • SQL Set Value of a Column Using COUNT(*)
  • SQL GROUP BY Column Value
  • Is it true that all joins following a left join in a SQL query must also be left joins? Why or why not?
  • Need SQL JOIN statement assistance
  • Unexpected results with PATINDEX
  • Oracle 11 query to combine multiple rows into 1 row
  • How to compare 2 converted dates in sql
  • From a bit complex SQL statement to preparedStatement
  • How to count distinct logins
  • SQL Server - Operand type clash: numeric is incompatible with datetimeoffset
  • Splitting 24 hours into 96 equal 15 minute intervals in T-SQL
  • WHERE clause not hitting on LEFT JOIN
  • SQL Pivot String Data
  • Redshift - Find % as compared to total value
  • How to find count of N columns in a table
  • SQL statement giving no result
  • IN condition is not working when the data is like 'Test','Test' from a result query
  • SUM of COUNT with only certain variables
  • How to get all products with at least X reviews in SQL?
  • sql Group by columns to the same row without join
  • Check if an integer is within the next 5 increments in SQL, when the numbers can wrap
  • Case and When query give me both column filled
  • How to check JSON string on contains field AND like value?
  • Find uncovered periods without exploding each combination
  • How to compare date to format date on oracle
  • Oracle - Combine timestamp with date and remove duplicates
  • Array to json and row to json in SQL Server 2016
  • Modifying SQL code to pick the Dropdown list instead of constant text
  • How to get max value of column values in a record ? (BigQuery)
  • Elasticsearch filter after aggregation
  • Teradata: varchar to timestamp 2
  • specific row number with all the fields
  • INSERT INTO SELECT query in SQL where primary key is used and multiple duplicates are in another table
  • SQL Query - Eliminate records
  • What is wrong with this SQL join query
  • Hybris Flexible search union query to fetch products
  • Stale Data Count
  • Adding new column of total_event
  • Query to Skip the first Delimiter and select the second Delimiter In SQL
  • Very slow (12+ hours) large table joins in postgres
  • LINQ query for specific group
  • SQL - return xpath for multiple XML results
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org