How to remove duplicates from table using join in mysql

Question

select * 
from
(
  SELECT  id, imei1, status
  FROM `owarranty_imei` mto 
  WHERE EXISTS
  (
    SELECT 1 
    FROM `owarranty_imei` mti 
    WHERE mto.imei1=mti.imei1 
    LIMIT 1, 1
  )
) t1 
left join `owarranty_warranty_activations` as t2 on t1.id=t2.imei_id
where t2.id is null 
limit 100

this is my query. In owarranty_imei has more than 100000 records. i want to get duplicates from imei table which owarranty_imei not in owarranty_warranty_activation table. This query work for few records but when i run it for more than 1000000 records its not working

17:56:48 SELECT id ,imei1 FROM `owarranty_imei` mto WHERE EXISTS(SELECT 1 FROM `owarranty_imei` mti WHERE mti.imei1 = mto.imei1 LIMIT 1, 1) LIMIT 0, 1000 Error Code: 2013. Lost connection to MySQL server during query 30.000 sec I got this error every time when i run this query — Sriya, Jan 19 '18 at 07:00
Clarify by editing your post into the best possible presentation, not in comments. Please read & act on [mcve]. In particualar for DDL/constraints. Presumably you are making a huge intermediate table, and presumably it's because you are making an inadvertent cross product and/or join on column sets that are not key that generate (too) many rows. — philipxy, Jan 19 '18 at 09:25

score 0 · Answer 1 · edited Jan 23 '18 at 06:49

0

  SELECT  
       mto.id, 
       mto.imei1, 
       mto.status
  FROM 
      `owarranty_imei` mto 
          INNER JOIN 
      `owarranty_imei` mti ON mto.imei1=mti.imei1 
          LEFT JOIN 
      `owarranty_warranty_activations` as t2 ON mto.id=t2.imei_id
  GROUP BY mto.id
  HAVING COUNT(t2.id)=0

edited Jan 23 '18 at 06:49

Sriya

167
2
3
10

answered Jan 19 '18 at 17:21

Alex

16,739
1
28
51

2

While this code snippet may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, as this reduces the readability of both the code and the explanations! – Blue Jan 19 '18 at 18:33

How to remove duplicates from table using join in mysql

1 Answers1