Removing Duplicates from SQL Join

Removing duplicates from SQL Join

You don't want to do a join per se, you're merely testing for existence/set inclusion.

I don't know what current flavor of SQL you're coding in, but this should work.

SELECT MAX(recid), firstname, lastname 
FROM table2 T2
WHERE EXISTS (SELECT * FROM table1 WHERE firstname = T2.firstame AND lastname = T2.lastname)
GROUP BY lastname, firstname

If you want to implement as a join, leaving the code largely the same:

i.e.

SELECT max(t2.recid), t2.firstame, t2.lastname 
FROM Table2 T2 
INNER JOIN Table1 T1 
    ON T2.firstname = t1.firstname and t2.lastname = t1.lastname
GROUP BY t2.firstname, t2.lastname 

Depending on the DBMS, an inner join may be implemented differently to an Exists (semi-join vs join) but the optimizer can sometimes figure it out anyway and chose the correct operator regardless of which way you write it.

SQL after the JOIN remove duplicate rows

If I correctly understand, you need this:

select T1.ID, T1.STATUS, t.OWNER from T1
inner join(
    select ID, OWNER from T2 group by ID, OWNER
) t
on T1.ID = t.ID 

Best way to combine two tables, remove duplicates, but keep all other non-duplicate values in SQL

If I understand your question correctly you want to join two large tables with thousands of columns that (hopefully) are the same between the two tables using the email column as the join condition and replacing duplicate records between the two tables with the records from Table 2.

I had to do something similar a few days ago so maybe you can modify my query for your purposes:

WITH only_in_table_1 AS(
SELECT *
FROM table_1 A
WHERE NOT EXISTS
(SELECT * FROM table_2 B WHERE B.email_field = A.email_field))
SELECT * FROM table_2
UNION ALL
SELECT * FROM only_in_table_1

If the columns/fields aren't the same between tables you can use a full outer join on only_in_table_1 and table_2

Remove duplicate left outer join

Try this:

SELECT
    a.[LocationID],
    a.[BuildingCode],
    a.[LocationCode],
    a.[LocationName],
    MAX(c.UserName) UserName,
    MAX(d.RoleName ) RoleName 
FROM 
    [dbo].[Location] a
LEFT OUTER JOIN 
    [dbo].[UserLocation] b ON a.LocationID = b.LocationID
LEFT OUTER JOIN 
    [dbo].[User] c ON b.UserID = c.UserID AND c.RoleID = 2
LEFT OUTER JOIN 
    [dbo].[Role] d ON c.RoleID = d.RoleID
GROUP BY 
    a.[LocationID],
    a.[BuildingCode],
    a.[LocationCode],
    a.[LocationName]

Left Join without duplicate rows from left table

Try an OUTER APPLY

SELECT 
    C.Content_ID,
    C.Content_Title,
    C.Content_DatePublished,
    M.Media_Id
FROM 
    tbl_Contents C
    OUTER APPLY
    (
        SELECT TOP 1 *
        FROM tbl_Media M 
        WHERE M.Content_Id = C.Content_Id 
    ) m
ORDER BY 
    C.Content_DatePublished ASC

Alternatively, you could GROUP BY the results

SELECT 
    C.Content_ID,
    C.Content_Title,
    C.Content_DatePublished,
    M.Media_Id
FROM 
    tbl_Contents C
    LEFT OUTER JOIN tbl_Media M ON M.Content_Id = C.Content_Id 
GROUP BY
    C.Content_ID,
    C.Content_Title,
    C.Content_DatePublished,
    M.Media_Id
ORDER BY
    C.Content_DatePublished ASC

The OUTER APPLY selects a single row (or none) that matches each row from the left table.

The GROUP BY performs the entire join, but then collapses the final result rows on the provided columns.

Remove duplicate columns after using join on in sql

Solution 1:
Specify the column names like this

SELECT
    t1.Name, t1.ItemNum, t1.TicketNum
FROM (
    SELECT TOP 100
        Name, ItemNum, TicketNum
    FROM [dbo].[dd]
) t1 
INNER JOIN (
    SELECT TOP 100
        TicketNum, ItemNum
    FROM [dbo].[dd]
) t2 ON t1.ItemNum = t2.ItemNum
    AND t1.TicketNum = t2.TicketNum

Solution 2:

SELECT t1.* FROM (
    SELECT TOP 100
        Name, ItemNum, TicketNum
    FROM [dbo].[dd]
) t1 
INNER JOIN (
    SELECT TOP 100
        TicketNum, ItemNum
    FROM [dbo].[dd]
) t2 ON t1.ItemNum = t2.ItemNum
    AND t1.TicketNum = t2.TicketNum

Removing duplicates from Join with grouped result

I would just use row_number():

select h.*
from (select h.*,
             row_number() over (partition by HardwareId order by timestamp desc) as seqnum
      from history h
     ) h
where seqnum = 1;

This will select exactly one row per device per day. If there are duplicates on the day, it will return an arbitrary value. If you want all of them, then use rank() instead of row_number().

Remove duplicates when joining tables

simple solution:

select * from news where news_id in (
select news_id 
from NewsType 
where type_id in (the types you want)
)

most people would say that you should add a DISTINCT on the news_id on the inner query. You can try that, but Im quite sure it will decrese performance.

Over all, if you think this solution doesnt perform well, you can make the inner query a CTE, which usually behaves better:

with my_CTE as(
    select news_id 
    from NewsType 
    where type_id in (the types you want)
)
select * 
from news 
where news_id in (select news_id  from my_CTE)

SQL Join Two Tables Remove Duplicates

You can use Inner join which will join tables such that it selects records that have matching values in both tables, write your query as

select distinct t1.mov_id, t1.mov_name, t2.actor_name from MOVIES t1 inner join ACTOR t2 on t1.actor_id=t2.actor_id_2;

---

Related Topics