Faire un SELECT WHERE sur un autre SELECT AS

Version imprimable

04/12/2020, 16h08
mecmec

Faire un SELECT WHERE sur un autre SELECT AS
Salut

Je montre ma commande SQL et ensuite j'explique son but :
Code:

1 2 3 4 5 6 select REPLACE(email_addr,'.','') as "email_a_tester" from compte where email_addr like '%@gmail.com' and email_addr not like '' group by REPLACE(email_addr,'.','') HAVING COUNT(email_addr) >= 1
Avec les commentaires:
select REPLACE(email_addr,'.','') as "email_a_tester" // je retire tous les points "." dans les adresses emails en créant une colonne propre avec "as" car sinon ça va créer une colonne avec le nom en "REPLACE(email_addr,'.','')" et c'est pas beau
from compte // la table
where email_addr like '%@gmail.com' and // je récupère seulement emails se terminant par %@gmail.com, pas les autres
email_addr not like '' // je ne récupère pas les quelques comptes créés autrefois avec un numéro de tél (qui sont sans adresse email)
group by REPLACE(email_addr,'.','') // c'est comme le select tout en haut du coup
HAVING COUNT(email_addr) >= 1 // je récupère toute la liste d'email pour les lister dans la colonne "email_a_tester"

Vous l'aurez compris, cette table elle liste toutes les adresses emails venant d'une table en enlevant tous les ".", donc "to.to@gmail.com" s’affichera en "toto@gmailcom".

Maintenant ce que je cherche à faire c'est comment devrais-je faire pour rechercher "toto@gmailcom" à partir de la colonne "email_a_tester" ?

PS: "email_a_tester" c'est un anti-doublons-gmail-avec-ou-sans.+, plus d'informations ici : https://support.google.com/mail/answer/7436150?hl=fr (c'est valable à la fois pour les "." et les "+" visiblement) - Actuellement je teste qu'avec les "." pour faire simple.

Cdt

Bonsoir,

De ce que je comprends de votre besoin, il suffit d'encapsuler votre requête dans une autre (utiliser une table dérivée donc) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 select [...] from ma_table as T1 inner join (select REPLACE(email_addr,'.','') as email_a_tester from compte where email_addr like '%@gmail.com' and email_addr not like '' group by REPLACE(email_addr,'.','') HAVING COUNT(email_addr) >= 1 ) as T2 on T2.email_a_tester=T1.courriel where [...]
On peut faire la même chose avec EXISTS, IN...

Par contre, comme il existe des adresses valides avec un point avant le @, pourquoi faire cette gymnastique ?

Code:

1 2 3 4 5 6 7 8 9 10 11 select T2.email_a_tester from compte inner join (select REPLACE(email_addr,'.','') as email_a_tester from compte where email_addr like '%@gmail.com' and email_addr not like '' group by REPLACE(email_addr,'.','') HAVING COUNT(email_addr) >= 1 ) as T2 on T2.email_a_tester="toto@gmailcom" limit 1
C'est super merci ça fonctionne bien

Citation:

Par contre, comme il existe des adresses valides avec un point avant le @, pourquoi faire cette gymnastique ?

Imaginons que tu crées un compte gmail et ton adresse principale c'est escartefigue@gmail.com, tu peux être contacter en modifiant l'email escartefigue@gmail.com par des "." ou des "+" (exemple: e.s.c.a.rte.fig.ue@gmail.com) du coup tu vas donc recevoir un email, c'est indiqué sur https://support.google.com/mail/answer/7436150?hl=fr et moi j'veux utiliser cette requête SQL pour lister toutes les adresses emails en retirant tous les "." (et "+" pour plus tard) et il faut donc retirer les "." et "+" de l'email à vérifier :
on T2.email_a_tester="toto@gmailcom" (sans . ni +)

Et cela bloqueras donc les contourneurs qui s’amusent à s'inscrire sur le site en rajoutant des "." dans n'importe quel endroit devant le "@".

Cdt

Salut à tous.

Je viens de lire ce document :
--> https://support.google.com/mail/answer/7436150?hl=fr

et je constate que la syntaxe des adresses email est très particulière chez google.
Il y a sûrement des petits malins qui ont abusé des adresses emails en jouant sur les point.

@ escartefigue : votre solution ne peut pas fonctionner car dans la table compte, l'email peut s'écrire de plusieurs façon.
En fait, vous ne récupérez que l'email sous cette forme "toto@gmail.com" et non ceux avec des points "t.o.t.o@gmail.com".

@ tous : la solution est de retourner toutes les lignes répondant aux critères, sans faire de produit cartésien.
Pour ce faire, il faut passer par une vue qui va extraire et mettre en forme toutes les lignes, sans les regrouper.
Puis faire une jointure entre la vue afin de donner les identifiant, et la même vue avec regroupement.
Ce qui donne :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
(  `id`      integer unsigned  NOT NULL auto_increment Primary key,
   `email`   varchar(255)      NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `test` (`email`) VALUES
  ('jean.du.pont@gmail.com'),
  ('jean.du.pont@outlook.com'),
  ('j.e.a.n.d.u.p.o.n.t@gmail.com'),
  ('je.an.du.po.nt@gmail.com'),
  ('jean-du-pont@gmail.com'),
 
  ('jean.du.pond@gmail.com'),
  ('jean.du.pond@outlook.com'),
  ('j.e.a.n.d.u.p.o.n.d@gmail.com'),
  ('jea.ndu.po.n.d@gmail.com'),
  ('je.an.du.po.nd@gmail.com'),
  ('jean-du-pond@gmail.com')
--------------
 
--------------
select * from  `test`
--------------
 
+----+-------------------------------+
| id | email                         |
+----+-------------------------------+
|  1 | jean.du.pont@gmail.com        |
|  2 | jean.du.pont@outlook.com      |
|  3 | j.e.a.n.d.u.p.o.n.t@gmail.com |
|  4 | je.an.du.po.nt@gmail.com      |
|  5 | jean-du-pont@gmail.com        |
|  6 | jean.du.pond@gmail.com        |
|  7 | jean.du.pond@outlook.com      |
|  8 | j.e.a.n.d.u.p.o.n.d@gmail.com |
|  9 | jea.ndu.po.n.d@gmail.com      |
| 10 | je.an.du.po.nd@gmail.com      |
| 11 | jean-du-pond@gmail.com        |
+----+-------------------------------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
   select  id,
           replace(`email`,'.','') as test
 
     from  `test`
    where  email like '%@gmail.com'
--------------
 
--------------
select * from `vue`
--------------
 
+----+-----------------------+
| id | test                  |
+----+-----------------------+
|  1 | jeandupont@gmailcom   |
|  3 | jeandupont@gmailcom   |
|  4 | jeandupont@gmailcom   |
|  5 | jean-du-pont@gmailcom |
|  6 | jeandupond@gmailcom   |
|  8 | jeandupond@gmailcom   |
|  9 | jeandupond@gmailcom   |
| 10 | jeandupond@gmailcom   |
| 11 | jean-du-pond@gmailcom |
+----+-----------------------+
--------------
select      t1.id,
            t1.test
      from  `vue` as t1
 
inner join (     select  test
                   from  `vue`
               group by  test
                 having  count(test) > 1
           ) as t2
        on  t2.test = t1.test
--------------
 
+----+---------------------+
| id | test                |
+----+---------------------+
|  1 | jeandupont@gmailcom |
|  3 | jeandupont@gmailcom |
|  4 | jeandupont@gmailcom |
|  6 | jeandupond@gmailcom |
|  8 | jeandupond@gmailcom |
|  9 | jeandupond@gmailcom |
| 10 | jeandupond@gmailcom |
+----+---------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Attention à dupont et dupond.

@+