List:General Discussion« Previous MessageNext Message »
From:mmanickalal Date:August 9 2002 8:49pm
Subject:Re: RES: Aggregate + left outer join
View as plain text  

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:             mmanickalal@stripped
     [SMTP:mmanickalal@stripped]
     Enviada em:             sexta-feira, 9 de agosto de 2002 17:13
     Para:           mysql@stripped
     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 <mysql-thread116761@stripped>
     To unsubscribe, e-mail
     <mysql-unsubscribe-eduardos=comolatti.com.br@stripped>
     Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php







Thread
Re: RES: Aggregate + left outer joinmmanickalal10 Aug