Eduardo,
Thanks for the reply, but I still get only one record for prj1. While prj2
does not show up with 0 hours.
CREATE TABLE PROJECT (
ID_PROJECT int(11) NOT NULL auto_increment,
NM_PROJECT varchar(255) NOT NULL default ''
PRIMARY KEY (ID_PROJECT)
) TYPE=MyISAM;
INSERT INTO PROJECT VALUES (100,'prj1');
INSERT INTO PROJECT VALUES (101,'prj2');
CREATE TABLE DAYLOG (
ID_PROJECT int(11) NOT NULL default '0',
DATE datetime NOT NULL default '0000-00-00 00:00:00',
HOURS float NOT NULL default '0',
PRIMARY KEY (ID_PROJECT,DATE)
) TYPE=MyISAM;
INSERT INTO DAYLOG VALUES (100,'2002-08-17 00:00:00',8);
INSERT INTO DAYLOG VALUES (100,'2002-08-18 00:00:00',8);
Thanks,
-Manu
|--------+--------------------------->
| | Eduardo Stopa |
| | <eduardos@comolat|
| | ti.com.br> |
| | |
| | 09-08-02 04:25 PM|
| | |
|--------+--------------------------->
>-----------------------------------------------------------------------|
| |
| To: manu manickalal/PRTGROUP |
| cc: |
| Subject: RES: Aggregate + left outer join |
>-----------------------------------------------------------------------|
Hi,
Try the following query :
SELECT
p.id_project, p.nm_project, sum( dl.hours)
FROM PROJECT p
LEFT OUTER JOIN DAYLOG dl ON (p.id_project = dl.id_project)
WHERE
(MONTH(dl.DATE) = 8 AND (YEAR(dl.DATE) = 2002) or (dl.DATE is
NULL)
GROUP BY p.id_project
Best Regards
Eduardo
----- Mensagem original -----
De: [EMAIL PROTECTED]
[SMTP:[EMAIL PROTECTED]]
Enviada em: sexta-feira, 9 de agosto de 2002 17:13
Para: [EMAIL PROTECTED]
Assunto: Aggregate + left outer join
Hi,
I am relatively new to MySQL.. I am having trouble getting an
aggregate
function and an outer join to work together.
I have two tables
PROJECT (id_project, nm_project)
100, prj1
101, prj2
DAYLOG (id_project, date, hours)
100, 2002-08-17, 8
100, 2002-08-18, 8
DAYLOG table contains number of hours booked against a project for a
date.
I needed to list the total hours booked against each project for any
given
month.
My query
SELECT
p.id_project, p.nm_project, sum( dl.hours)
FROM PROJECT p
LEFT OUTER JOIN DAYLOG dl ON (p.id_project = dl.id_project)
WHERE
MONTH(dl.DATE) = 8
AND YEAR(dl.DATE) = 2002
GROUP BY p.id_project
+------------+------------+------------------------------------+
| id_project | nm_project | sum( dl.hours) |
+------------+------------+------------------------------------+
| 100 | prj1 | 16 |
+------------+------------+------------------------------------+
The problem with this result set is that it does not have projects
with
zero hours booked. ie. . if there are no records in DAYLOG, then that
project is not displayed. I was expecting the left outer join to pick
up
records (prj2) from PROJECT even if there are no records in DAYLOG.
So I tried another variation with the select
SELECT p.id_project, p.nm_project, sum( dl.hours)+ IFNULL(dl.hours,0)
added the IFNULL. Yet the query lists only projects which have records
for
the given month.
Any insight, help, thoughts etc are greatly appreciated.
Thanks,
-Manu
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
