A Simple Query Help
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 = XX 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
Re: A Simple Query Help
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 = ‘XX’) 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 = ‘XX’ 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 = ‘XX’ 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 = ‘XX’ 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Why does the limit use the early row lookup.
Why does not the mysql developer team to do this optimization? --- 12年4月20日，周五, Reindl Harald h.rei...@thelounge.net 写道： 发件人: Reindl Harald h.rei...@thelounge.net 主题: Re: Why does the limit use the early row lookup. 收件人: mysql@lists.mysql.com 日期: 2012年4月20日,周五,下午3:50 Am 20.04.2012 04:29, schrieb 张志刚: My point is that the limit can use late row lookup: lookup rows after checking indexes to optimize the select speed. But the mysql optimizer do it with the early row lookup: lookup all rows before checking indexes when the one fetch column is not in the indexes. Tell me why? because the mysql optimizer until now is really bad in many situations - order by rand() makes a temporary table wil ALL data as example even with limit select * from table order by rand() limit 10; reads and writes the whole table to disk have fun with large tables :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
