This works perfectly, you're a genius :-)
I will have to have a rethink about how to write SQL querries for mysql.
Thanks again.
Jeff.
"Kalok Lo"
<kalokl@shopa. To: "Jeff Rouse/NCO/CEtv"
<[EMAIL PROTECTED]>
org> cc:
Subject: Re: Question about left join.
10/24/2001
10:44 PM
Please respond
to "Kalok Lo"
I believe any type of limiting on the right side of the table needs to go
within the left join "on" clause.
Give this a try, note, I changed it from a natural left join to regular
left
join, so the syntax is different, but that's the only way I know how to
write it.
Let me know how it works:
select u.username Who, ifnull(count(d.username),0) Logs
from users as u left join details as d
on u.username = d.username
and d.entrydate > date_add(now(), interval -7 day )
where u.disabled='N'
group by u.username
order by 2 desc
----- Original Message -----
From: "Jeff Rouse/NCO/CEtv" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 23, 2001 10:17 PM
Subject: Question about left join.
>
> 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
***************************************************************************************************************************************************************************
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
