Gasire duplicate in mai multe coloane in Mysql

Discutii despre script-uri si coduri PHP-MySQL, precum si lucru cu XML in PHP.
giulian9
Mesaje: 39

Gasire duplicate in mai multe coloane in Mysql

Incerc sa gasesc daca adresa de email apare de mai multe ori in tabel , iar acolo unde se gaseste potrivire sa-mi afiseze id-ul .
Eu am folosit ceva de genul :

Cod: Selectaţi tot

 SELECT REC_ID FROM ADRESSEN WHERE EMAIL IN ( 
 SELECT EMAIL FROM ADRESSEN group by EMAIL having count(EMAIL) > 1 ) ORDER BY EMAIL ;
Functioneaza , verifica potrivirile Email=Email , insa nu am idee cum as putea face mai complexa interogarea , de ex:
EMAIL = EMAIL , EMAIL = EMAIL2 ( Email2 e alt camp din tabel ) si EMAIL2=EMAIL2
Multumesc anticipat.

MarPlo
Salut
Ma pricep putin la instructiunile mysql. Incearca sa aplici "GROUP BY" la ambele coloane unde vrei sa cauti duplicate:

Cod: Selectaţi tot

 SELECT REC_ID FROM ADRESSEN WHERE EMAIL IN (
 SELECT EMAIL FROM ADRESSEN group by EMAIL, EMAIL2 having count(EMAIL) > 1 ) ORDER BY EMAIL;
Sau ceva mai complex, cu Join:

Cod: Selectaţi tot

SELECT  REC_ID FROM ADRESSEN 
JOIN
(
  SELECT EMAIL, EMAIL2, Count(*) as nrc FROM ADRESSEN 
  GROUP BY  EMAIL, EMAIL2
  HAVING nrc > 1
) sub0
ON sub0.EMAIL = ADRESSEN .EMAIL 
AND sub0.EMAIL2 = ADRESSEN .EMAIL2
- Daca nu functioneaza cum vrei, vezi ce gasesti pe internet la cautare:
" find same duplicate in multiple columns in mysql ".

giulian9
Postez si solutia gasita de mine , nu pare prea eleganta insa "merge" , am folosit trei interogari: una pentru duplicatele din coloana 1:

Cod: Selectaţi tot

SELECT REC_ID FROM ADRESSEN WHERE EMAIL IN ( SELECT EMAIL FROM ADRESSEN group by EMAIL having count(EMAIL) > 1 ) ORDER BY EMAIL
Asemanator pt coloana EMAIL2 .
Pt a compara cele 2 coloane am folosit ceva de genul:

Cod: Selectaţi tot

SELECT DISTINCT REC_ID from ADRESSEN t1,
(SELECT EMAIL,EMAIL2 FROM ADRESSEN ) t2 where t1.EMAIL=t2.EMAIL2 or t1.EMAIL2=t2.EMAIL order by 1 asc
Sper sa ajute pe cineva ...candva ! ;)

Subiecte similare