HELP with SQL subselect

i am trying a query like

select email from table_1 where email not in (select email from
table_2);


for some reason, i am getting (at least one) email rows returned which
also appear in table_2 ...
i have tried using LOWER(email) for both selects, thought the email i
am getting is all lowercase in both tables.

my goal is to only return emails that appear in table_1 but not if they
also appear in table_2.

is there some way to handle this with LIKE or am i missing something in
that these are 'chars' (could the @ in emails be causing problems)?


might there be some other way to do this - using mysql 5.x


many thanks,
s7

starman7@hotmail.com wrote:

> i am trying a query like
>
> select email from table_1 where email not in (select email from
> table_2);
>
>
> for some reason, i am getting (at least one) email rows returned which
> also appear in table_2 ...
> i have tried using LOWER(email) for both selects, thought the email i
> am getting is all lowercase in both tables.
>
> my goal is to only return emails that appear in table_1 but not if they
> also appear in table_2.
>
> is there some way to handle this with LIKE or am i missing something in
> that these are 'chars' (could the @ in emails be causing problems)?
>
>
> might there be some other way to do this - using mysql 5.x
>
>
> many thanks,
> s7
>
This can be faster than subselects...

first, need more information - like what is the relationship between table1 and
table2?

select a.email from table1 a left join table2 b on a.email=b.email where b.email
is null;


--
Michael Austin.
Database Consultant

Michael Austin wrote:
> starman7@hotmail.com wrote:
>
> > i am trying a query like
> >
> > select email from table_1 where email not in (select email from
> > table_2);
> >
> >
> > for some reason, i am getting (at least one) email rows returned which
> > also appear in table_2 ...
> > i have tried using LOWER(email) for both selects, thought the email i
> > am getting is all lowercase in both tables.
> >
> > my goal is to only return emails that appear in table_1 but not if they
> > also appear in table_2.
> >
> > is there some way to handle this with LIKE or am i missing something in
> > that these are 'chars' (could the @ in emails be causing problems)?
> >
> >
> > might there be some other way to do this - using mysql 5.x
> >
> >
> > many thanks,
> > s7
> >
> This can be faster than subselects...
>
> first, need more information - like what is the relationship between table1 and
> table2?
>
> select a.email from table1 a left join table2 b on a.email=b.email where b.email
> is null;


thanks for the reply michael -

there is no relationship between the two tables - only the email which
may be the same in either - with the added problem that the case may be
different - any way to deal with this (w/ the lower keyword)?

also to add another wrinkle, i would actually like to exclude from a
third table - can two left joins be used? it's less complex with two
NOT IN subselects (would that be an OR or an AND between the two
subselects?) - anyway - i used your example and still an email address
that was in table2 and also in table1 is coming back in my resultset
(it's the same case in both tables)

the columns are both varchar255 - and there are no spaces in either
result.

select a.email from t1 a left join t2 b on a.email=b.email where
b.email
is null;


thanks again,
s7


>
>
> --
> Michael Austin.
> Database Consultant

starman7@hotmail.com wrote:

> Michael Austin wrote:
>
>>starman7@hotmail.com wrote:
>>
>>
>>>i am trying a query like
>>>
>>>select email from table_1 where email not in (select email from
>>>table_2);
>>>
>>>
>>>for some reason, i am getting (at least one) email rows returned which
>>>also appear in table_2 ...
>>>i have tried using LOWER(email) for both selects, thought the email i
>>>am getting is all lowercase in both tables.
>>>
>>>my goal is to only return emails that appear in table_1 but not if they
>>>also appear in table_2.
>>>
>>>is there some way to handle this with LIKE or am i missing something in
>>>that these are 'chars' (could the @ in emails be causing problems)?
>>>
>>>
>>>might there be some other way to do this - using mysql 5.x
>>>
>>>
>>>many thanks,
>>>s7
>>>
>>
>>This can be faster than subselects...
>>
>>first, need more information - like what is the relationship between table1 and
>>table2?
>>
>>select a.email from table1 a left join table2 b on a.email=b.email where b.email
>>is null;
>
>
>
> thanks for the reply michael -
>
> there is no relationship between the two tables - only the email which
> may be the same in either - with the added problem that the case may be
> different - any way to deal with this (w/ the lower keyword)?
>
> also to add another wrinkle, i would actually like to exclude from a
> third table - can two left joins be used? it's less complex with two
> NOT IN subselects (would that be an OR or an AND between the two
> subselects?) - anyway - i used your example and still an email address
> that was in table2 and also in table1 is coming back in my resultset
> (it's the same case in both tables)
>
> the columns are both varchar255 - and there are no spaces in either
> result.
>
> select a.email from t1 a left join t2 b on a.email=b.email where
> b.email
> is null;
>
>
> thanks again,
> s7
>
>
>
>>
>>--
>>Michael Austin.
>>Database Consultant
>
>

an easier way to deal with email addresses is to use lower() when you insert
them, that way there is less data processing when needing to do the selects

(to modify what you currently have simply:
update tablea set email=lower(email);
this will change all email address to lower case. If you are using a web-page
to insert/query/login use a function to lowercase the user input to match the
database.
)

adding where clause an

select a.email,b.email,c.email from t1 a
left join t2 b
on lower(a.email)=lower(b.email)
left join t3 c
on lower(a.email)=lower(c.email);
+---------+---------+---------+
| email | email | email |
+---------+---------+---------+
| a@b.com | a@b.com | a@b.com | <<exists in all 3
| b@c.com | b@c.com | NULL | <<exists in t1,t2
| c@d.com | NULL | c@d.com | <<exists in t1,t3
+---------+---------+---------+
3 rows in set (0.04 sec)

The BASE query;
mysql> select a.email,b.email,c.email from t1 a left join t2 b on lower(a.email
)=lower(b.email) left join t3 c on lower(a.email)=lower(c.email);
+---------+---------+---------+
| email | email | email |
+---------+---------+---------+
| a@b.com | a@b.com | a@b.com |
| b@c.com | b@c.com | NULL |
| c@d.com | NULL | c@d.com |
+---------+---------+---------+
3 rows in set (0.04 sec)

ROWS in A but may not exist in B or in C
mysql> select a.email,b.email,c.email from t1 a left join t2 b on lower(a.email
)=lower(b.email) left join t3 c on lower(a.email)=lower(c.email) where b.email i
s null or c.email is null;
+---------+---------+---------+
| email | email | email |
+---------+---------+---------+
| b@c.com | b@c.com | NULL |
| c@d.com | NULL | c@d.com |
+---------+---------+---------+
2 rows in set (0.02 sec)

ROWS in a but may exist in b or c
mysql> select a.email,b.email,c.email from t1 a left join t2 b on lower(a.email
)=lower(b.email) left join t3 c on lower(a.email)=lower(c.email) where b.email i
s not null or c.email is not null;
+---------+---------+---------+
| email | email | email |
+---------+---------+---------+
| a@b.com | a@b.com | a@b.com |
| b@c.com | b@c.com | NULL |
| c@d.com | NULL | c@d.com |
+---------+---------+---------+
3 rows in set (0.04 sec)


Records that exist in all 3 tables;
mysql> select a.email,b.email,c.email from t1 a left join t2 b on lower(a.email
)=lower(b.email) left join t3 c on lower(a.email)=lower(c.email) where b.email i
s not null and c.email is not null;
+---------+---------+---------+
| email | email | email |
+---------+---------+---------+
| a@b.com | a@b.com | a@b.com |
+---------+---------+---------+
1 row in set (0.08 sec)


or this will find rows where the email ONLY exists in t1.
First - let's add a row to t1 only:

mysql> insert into t1 values ('d@f.com');
Query OK, 1 row affected (0.22 sec)

select f.email from t1 f where f.email not in (
select a.email
from t1 a left outer join t2 b
on a.email=b.email
left outer join t3 c
on a.email=c.email
where b.email is not null or c.email is not null
);

+---------+
| email |
+---------+
| d@f.com |
+---------+


--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)