From: Peter Brawley
Date: August 10 2006 4:07pm
Subject: Re: joining tables
List-Archive: http://lists.mysql.com/mysql/200833
Message-Id: <44DB59AB.6040504@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Reinhart,

 >So even the clients who have no entry in events on that day, but they have
 >an event in that specific month and year should be shown with a 0 value.

Try ...FROM clients LEFT JOIN events...

PB

-----

Reinhart Viane wrote:
> Table1: events
> Duration 	workdate 	clientid 	personid
> 60		2006-01-03	1		51
> 48		2006-01-03	2		51
> 167		2006-01-03	4		51
>
> Table2: clients
> Clientid 	name
> 1		client1	
> 2		client2
> 3		client3
> 4		client4
>
> select SUM(duration) as totaltime, name from events left join clients on
> (events.clientid=clients.clientid) where MONTH(events.workdate)=1 and
> YEAR(events.workdate)=2006 and DAY(events.workdate)=3 and events.personid=51
> group by events.clientid desc
>
> I used this query and this give s me this result:
> Totaltime 	name  
> 60		client1
> 48		client2
> 167		client4
>
>
> But what I need is a result like this:
> Totaltime 	name  
> 60		client1
> 48		client2
> 0		client3
> 167		client4
>
> So even the clients who have no entry in events on that day, but they have
> an event in that specific month and year should be shown with a 0 value.
> Any help really appreciated.
>
> Thx in advance
>
>
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.10.8/415 - Release Date: 8/9/2006