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: Re: Simple query help
El vie, 29-08-2003 a las 22:05, Daniel Clark escribió: select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? What about: SELECT value, date FROM tablename WHERE id = 4 ORDER BY date ASC Just pick the first row. Thanks Daniel. Yes, it's the last option. But, I think that there is a good one. I think that somebody had this problem before. Does anybody have more ideas? Thanks in advance and best regards boricles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple query help
* Boris Villazon El vie, 29-08-2003 a las 22:05, Daniel Clark escribió: select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? What about: SELECT value, date FROM tablename WHERE id = 4 ORDER BY date ASC Just pick the first row. Thanks Daniel. Yes, it's the last option. But, I think that there is a good one. I think that somebody had this problem before. Does anybody have more ideas? You wanted tha latest date, right? You should use what Daniel suggested, exept you probably want ...ORDER BY date DESC LIMIT 1 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple query help
Hi I have a little problem with my sql skills. I have a table with the following fields: id (int) | value (varchar) | date (date) I need to show for a given id the value of the oldest date. Normally, I'd do something like this: select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? Thanks in advance and best regards boricles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple query help
select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? What about: SELECT value, date FROM tablename WHERE id = 4 ORDER BY date ASC Just pick the first row. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
