Mysql v5.7 I know this question is asked and answered before but the general queries is not working in my database and im pulling my hair off. I have two tables that only have one column containing emails : 1) Sent (41110 rows [there are duplicates]) 2) Blocks (81132 [there are duplicates])
mysql> show create table blocks;
+--------+------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------+
| blocks | CREATE TABLE `blocks` (
`email` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------------------------+
mysql> show create table sent;
+-------+-----------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------+
| sent | CREATE TABLE `sent` (
`emails` varchar(111) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------+
I want to get rows as a result of "Sent" - "Blocks" OR I want to get those rows from "Sent" table that is not in "Blocks" table
Sent table:
mysql> select * from sent limit 10;
+-----------------------------------------------------+
| emails |
+-----------------------------------------------------+
| "52@mail.marketplace" |
| "1cstains@coden.com" |
| "a.aldhizer@ldplastics.com" |
| "a.antosca@tdx.com" |
| "a.balvanz@hrrd-paper.com" |
| "a.bandal@medpace.com" |
| "a.bass@paettethanol.com" |
| "a.bodwalk@bedfndustries.com" |
| "a.bravo@sdfhemie.com" |
| "a.burton@evdarenterprises.com" |
+-----------------------------------------------------+
10 rows in set (0.00 sec)
Blocks
mysql> select * from blocks limit 10;
+---------------------------------------------------+
| email |
+---------------------------------------------------+
| "" |
| 0f6a88be0a4b45628be38ae08c8fdd71@mail.marketplace |
| 1cstains@coden.com |
| 1cstains@coden.com |
| 1cstains@coden.com |
| 3161foodmgr@mcare.com |
| 4b00fce87e5b423c942f5b19f27c3a13@mail.marketplace |
| 52d05d98b59e44b0816401d2cd0411f0@mail.marketplace |
| 52d05d98b59e44b0816401d2cd0411f0@mail.marketplace |
| 6357a2fd35114418a93e0ccda6edd6f4@mail.marketplace |
+---------------------------------------------------+
10 rows in set (0.00 sec)
Expected result from Sent table
+-----------------------------------------------------+
| emails |
+-----------------------------------------------------+
| "52@mail.marketplace" |
| "a.aldhizer@ldplastics.com" |
| "a.antosca@tdx.com" |
| "a.balvanz@hrrd-paper.com" |
| "a.bandal@medpace.com" |
| "a.bass@paettethanol.com" |
| "a.bodwalk@bedfndustries.com" |
| "a.bravo@sdfhemie.com" |
| "a.burton@evdarenterprises.com" |
+-----------------------------------------------------+
Here is the query i used and it returns matching rows :/
select sent.emails from sent where sent.emails NOT IN ( select email from blocks )
select sent.emails
from sent
left join blocks on sent.emails = blocks.email
where blocks.email is null
if the problem is " char in emails (as @Jens was mentioned) you can change join to cut the " char using subbstring to skip first and last chars
left join blocks on SUBSTRING(sent.emails,1,len(sent.emails)-2) = blocks.email