Merge two tables and then delete rows that match in another table

Question

I am working on a question where I have two tables. They share the same columns, so I did a union all on them. I'm not sure if that's the best way. I am supposed to delete the rows that match in another table and I am not getting that right. I'm trying to delete the rows from the third table that have the same username as in the joined tables 1 & 2. Also how do I name the newly joined table? I use R a lot so am used to creating new tables but not sure here.

Table 1, 2, and 3 have 3 columns, all the same. They are Company, Username, and Time. Table 1, 2 are merged. If a username is in Table 3 and the Table 1,2, we want that dropped in Table 1,2.

Example of what the data looks like I want to merge table 1 and 2 and remove entries that have a username in table 3. Table 3 is not being added to these tables.

table1
Company    Username    Time
Company1   Bobbio123   23:77
Company1   Yellow8     12:12
Company1   Travis9     14:16
Company1   Leonardy    05:00
Company1   Yessir      06:00

table 2
Company2   Sally111    20:10
Company2   TammyTammy  02:11
Company2   ScammyDavis 06:17

table 3
Company3   Dannyeh     12:20
Company3   ScammyDavis 01:11
Company3   Leonardy    05:00

What I want

Company    Username    Time
Company1   Bobbio123   23:77
Company1   Yellow8     12:12
Company1   Travis9     14:16
Company1   Yessir      06:00
Company2   Sally111    20:10
Company2   TammyTammy  02:11

Here is what I've done:

SELECT * FROM table1
UNION ALL
SELECT * FROM table2

DELETE FROM table1
WHERE table1.username = table3.username OR table2.username = table3.username;

I've tried this as well and worked on one table only but it didn't work.

SELECT * FROM table1, table3
DELETE FROM table1
WHERE table1.username = table3.username

Please check out https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join — Jan, Sep 27 '22 at 15:55
I read this and was already looking at the types of joins on other sites. So are you saying I have to do a full join and then an outer join to drop the rows that match in the 3rd table? — Dr. Spock, Sep 27 '22 at 16:14
it is hard to comprehend what you, you trow 3 tables and it is not clear which dara should be erased where — nbk, Sep 27 '22 at 17:18
This should return only Table1 records that have the same username as Table2 SELECT * FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.username = T2.username WHERE T2.username IS NULL — Jan, Sep 27 '22 at 17:21
As @nbk pointed out: please clarify what you're trying to delete. In the text you wrote that you want to remove the records from the "third table" but the delete statements use table1. Then, there's a SELECT, which is just a query, followed by a DELETE. You can't delete from a query. So, please, describe each step exactly, perhaps with some sample data and the desired result. — Mihe, Sep 27 '22 at 17:26
I've edited it as requested so I hope my example clarifies what I'm looking for. — Dr. Spock, Sep 27 '22 at 18:21
It is still not clear. Please add a few more rows in your table data to illustrate it. — Rohit Gupta, Sep 27 '22 at 20:40
So you want all records from T1,T2 where the username in T1,T2 set doesn't exist in T3? — xQbert, Sep 28 '22 at 18:05

Jan · Answer 1 · 2022-09-28T17:50:30.930

You need to identify all three tables, this is a suggestion. I'm GUESSING that you want to delete from Table1 when the username is in either Table2 or Table3 Please TEST first to see if this meets your criteria THEN add your delete clause

SELECT * 
FROM Table1 T1 LEFT JOIN 
     Table2 T2 ON T1.username = T2.username LEFT JOIN
     Table3 T3 ON T1.username = T3.username
WHERE T2.username IS NULL OR T3.username IS NULL
<or>
WHERE T2.username IS NOT NULL AND T3.username IS NOT NULL

From your comments you just want to report unique username regardless of the table it's in.

    --Combine all three tables into #Temp1
    SELECT * 
    INTO #Temp1
    FROM Table1
    UNION ALL
    SELECT *
    FROM Table2
    UNION ALL
    SELECT *
    FROM Table3

    --group by username with a tally into #Temp2
    SELECT MIN(Company) Company, Username, MAX([Time]) [Time], sum(1) Kt
    INTO #Temp2
    FROM #Temp1
    GROUP BY Username

    --select unique username
    SELECT Company, Username, [Time]
    FROM #Temp2
    WHERE Kt = 1
    ORDER BY Company, Username

I'm dropping rows that are in T1 & T3 OR T2 & T3 so the last join isn't creating the desired outcome. Thanks though. I'm sure I could use this somewhere — Dr. Spock, Sep 28 '22 at 15:28

Merge two tables and then delete rows that match in another table

1 Answers1