Question 263309 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Compare two tables to check if table two contains ids based on table 1 condition

I have two tables, T1 which is the original table and the T2 which is an exact copy of T1 until I make changes for it.

At the start both T1 and T2 looks as follows:

ID Event Event_Date
001 exam – english 2022-04-04
001 exam – biology 2021-03-01
002 exam – maths 2022-05-23
002 exam – english 2022-05-29
003 exam – chemistry 2022-04-13
004 exam – maths 2021-09-15

I’ve used the following SQL to alter T2:

DELETE FROM T2 
WHERE ID IN (SELECT ID FROM T2 WHERE EVENT_DATE < '2022-01-01')

And I should get something that looks like the following:

ID Event Event_Date
002 exam – maths 2022-05-23
002 exam – english 2022-05-29
003 exam – chemistry 2022-04-13

What I want to do now is just a quality check to make sure that I have deleted all rows where an ID has ever had an event_date < 2022-01-01.

I’ve tried to do the following:

SELECT T1.ID 
FROM T1 
INNER JOIN T2 ON T1.ID=T2.ID 
WHERE T1.EVENT_DATE < '2022-01-01'

But I think that’s just trying to look for dates < 2022-01-01 which shouldn’t be in T2 anyway so I don’t think that this is the correct way of solving the problem.

Tags:

2

Answers


  1. 0

    It’s simple. Just use Left join instead of Inner join in the query that you have mentioned. It will give you your desired output. Also, you need to add ID column of T2 table so that the NULL values corresponding to ID column of T1 table will help you know which ID was deleted from T2.

    SELECT T1.ID as T1_ID, T2.ID as T2_ID
FROM T1 
LEFT JOIN T2 ON T1.ID=T2.ID 
WHERE T1.EVENT_DATE < '2022-01-01'
    Login or Signup to reply.
  2. 0

    You can use Left join in place of inner join in the query.

    SELECT T1.ID 
FROM T1 
LEFT JOIN T2 ON T1.ID=T2.ID 
WHERE T1.EVENT_DATE < '2022-01-01'
    • It’ll return pairs of T1.ID and T2.ID values where T1.EVENT_DATE is before 2022-01-01, and if there’s no match in T2, it will still return T1.ID with a NULL value for T2.ID.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search