Hi
I posted the question below a little over a week ago and I have not had any responses. Can anyone let me know if my email lacked detail and that's why I've had no responses or whether this is a bug? I can create a complete script that creates the tables, populates them and runs the querries and post that if it will help. Thankyou Jeff. ----------------------------------------------- Hi I have two tables that are basically: users --------- username details ---------- id entrydate username subject contents I am trying to retrieve records from the details table of all users who have logged a job. If a user hasn't then I want them to show 0. When I run the left join query below without specifying a date from the details table it works fine as in Example 1 below. When I add the line, "and d.entrydate > date_add(now(), interval -7 day )" the left join no longer seems to work. Does anyone know what I am doing wrong? This type of query works fine in Oracle, where the left join is replaced by an outer table join on username. Note: Originally I didn't have a natural left join I just had a left join and specified u.username=d.username but had the same results. I know that the two are syntactically the same, but thought it worth mentioning. Example 1. - Nulls returned. ----------------- select u.username Who, ifnull(count(d.username),0) Logs from users as u natural left join details as d where u.disabled='N' group by u.username order by 2 desc Who Logs ------- -------- bob 9 sarah 8 frank 3 jane 0 jack 0 Example 2. - No Nulls returned. ----------------- select u.username Who, ifnull(count(d.username),0) Logs from users as u natural left join details as d where u.disabled='N' and d.entrydate > date_add(now(), interval -7 day ) group by u.username order by 2 desc Who Logs ------- -------- bob 7 sarah 3 frank 1 Thankyou Jeff. *************************************************************************************************************************************************************************** This email and any files transmitted with it, are confidential and is intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error, please notify the system manager. This footnote also confirms that this email message has been scanned by AUSTAR Communications content and virus scanning applications for the presence of computer viruses. *************************************************************************************************************************************************************************** --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php ========================================================= This message has been scanned for viruses by AUSTAR Communication's antivirus and content checking applications. Austar Communications ========================================================= *************************************************************************************************************************************************************************** This email and any files transmitted with it, are confidential and is intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error, please notify the system manager. This footnote also confirms that this email message has been scanned by AUSTAR Communications content and virus scanning applications for the presence of computer viruses. *************************************************************************************************************************************************************************** --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
