From: Igor Shevtsov
Date: April 22 2012 9:45pm
Subject: Re: A Simple Query Help
List-Archive: http://lists.mysql.com/mysql/227227
Message-Id: <4F947BF7.4050903@googlemail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit

Hi Rafael,
You can try using correlated subquery instead of outer join. This can be slow with big tables though:

SELECT * FROM users WHERE accept_email = 1 and email not in (SELECT email FROM sent_emails WHERE sent_emails
.email = users.email AND messageID NOT LIKE = ‘XXXXXX’) 

OR OUTER JOIN as a better option:

SELECT u.* FROM users AS u OUTER LEFT JOIN sent_emails AS se USING (email) where u.accept_email = 1 AND se.messageID NOT LIKE = ‘XXXXXX’ AND se.email IS NULL



Thanks,
Egor




SELECT * FROM sent_emails WHERE email in (SELECT email FROM users WHERE accept_email = 1)
AND messageID NOT LIKE = ‘XXXXXX’



On 04/22/2012 09:30 PM, Rafael Ribeiro wrote:
> Dear Friends,
>
>  
>
> I m new on this list, and I m trying to learn more about mysql.
>
>  
>
> After perform a lot of searchs in the Internet, I have no answer to my
> question and would like to ask your help.
>
>  
>
> I wanna a perform a query that depends of the result from another (query)
> table inside the same database.
>
>  
>
> On this scenario:
>
>  
>
> I have 02 tables:
>
>  
>
> Table 1 => users
>
> Table 2 => sent_emails
>
>  
>
>  
>
> I wanna select ONLY the users that are NOT inside the table SENT_emails
>
>  
>
> Example:
>
>  
>
> $query1 = SELECT * FROM users WHERE accept_email = ‘1’ 
>
>  
>
> The results from query above SHOULD depends of the query Bellow:
>
>  
>
> $query2 = SELECT * FROM sent_emails WHERE email = $email_from_query_above
> AND messageID NOT LIKE = ‘XXXXXX’
>
>  
>
> The results of the first query, should display only the users that are NOT
> inside the condition of query 2.
>
>  
>
> I read about INNER JOIN LEFT ... but I can´t understand ...
>
>  
>
> Can help me?
>
>  
>
> With Regards,
>
> Rafael Ribeiro
>
>  
>
>  
>
>  
>
>  
>
>  
>
>  
>
>