Mysql Query On Joins

Question

I have two tables

Table1

      SNO RECEIVER USER1 USER2
      1   133       44   45
      2   144       66   77
      3   155       77   33

AND

TABLE 2

     RECEIVER USER
     133      44
     133      45
     144      88
     144      55
     155      77

Required : Delete all rows from table1 which do not have matching entry in table2

example

     2   144       66   77

shoul be deleted as there are no row of 144 66 on table2

155 33

I TRIED BUT ITS NOT WORKING DELETE FROM TABLE1 A LEFT JOIN TABLE2 B ON (A.RECEIVER=B.RECEIVER AND A.USER1=B.USER) AND B.USER IS NULL;

i know joins but this seems too hard – Shakini Apr 25 '15 at 18:56 — Shakini, Apr 25 '15 at 18:56

score 1 · Accepted Answer · answered Apr 25 '15 at 19:02

You can use a JOIN inside a DELETE statement, too. The following statement joins (using a left join!) the two tables, selects the result rows which have no corresponding record from table 2 (WHERE ...) and deletes the respective rows from t1:

DELETE t1
FROM t1 LEFT JOIN t2 ON (t1.RECEIVER = t2.RECEIVER AND t1.USER1 = t2.USER)
WHERE t2.RECEIVER IS NULL

See the manual for reference, especially the "Multi-Table Deletes" section.

mysqlrockstar · Answer 2 · 2015-04-25T19:10:39.980

0

Why need JOIN to do this, rather use SUB-QUERY

 DELETE FROM  table1
     WHERE receiver NOT IN ( SELECT DISTINCT (receiver) FROM table2 )
     AND user1 NOT IN ( SELECT DISTINCT (user) FROM table2 );

edited Apr 25 '15 at 19:10

answered Apr 25 '15 at 19:02

mysqlrockstar

2,536
1
19
36

score 0 · Answer 3 · answered Apr 25 '15 at 19:10

Delete * from Table1 
Where not exists (select 1 from Table2 where 
RECEIVER =  Table1.RECEIVER and (user = Table1.user1 or user = Table1.user2))

Clarify what is meant by not matching. What do you consider matching. In my answer above I assumed that matching meant same receiver and either user1 or user2 is same as user in Table2. If it is not the case the query can be modified.

score 0 · Answer 4 · answered Apr 25 '15 at 19:10

Sth like:

SELECT t1.sno FROM table1 t1 LEFT JOIN table2 t2 ON t1.receiver = t2.receiver and 2.user = t1.user1 WHERE t2.receiver IS NULL;

above query joins those 2 tables by receiver and user columns and selects only those records where there is no matching record(left join with select all records from table1 while WHERE t2.receiver IS NULL will select only those that didnt have a matching record in table2)

now you can do

delete from table1 where sno IN (__PASTE_ABOVE_QUERY__);

Mysql Query On Joins

4 Answers4