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 |
+-----------------------------------------------------+
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
+-----------------------------------------------------+
10 rows in set (0.00 sec)
Blocks
mysql> select * from blocks limit 10;
+---------------------------------------------------+
| email |
+---------------------------------------------------+
| "" |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
+---------------------------------------------------+
10 rows in set (0.00 sec)
Expected result from Sent table
+-----------------------------------------------------+
| emails |
+-----------------------------------------------------+
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
| "[email protected]" |
+-----------------------------------------------------+
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