List:General Discussion« Previous MessageNext Message »
From:Reinhart Viane Date:August 10 2006 3:18pm
Subject:joining tables
View as plain text  
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

joining tablesReinhart Viane10 Aug
  • Re: joining tablesPeter Brawley10 Aug