Query LEFT OUTER JOIN

[viki1967]

Hi there.

I just tested this using mysql and it NOT works.

Here are the table structures I used:

/*Table structure for table `tabella_completa` */

CREATE TABLE `tabella_completa` (
  `Nome` varchar(64) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `tabella_completa` */

insert  into `tabella_completa`(`Nome`) values ('Miki');
insert  into `tabella_completa`(`Nome`) values ('Viki');
insert  into `tabella_completa`(`Nome`) values ('Jim');

/*Table structure for table `tabella_nomi` */

CREATE TABLE `tabella_nomi` (
  `Id` int(11) NOT NULL auto_increment,
  `Nome` varchar(64) default NULL,
  `DataInizio` varchar(64) default NULL,
  `DataFine` varchar(64) default NULL,
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `tabella_nomi` */

insert  into `tabella_nomi`(`Id`,`Nome`,`DataInizio`,`DataFine`) values (1,'Miki','2008-02-22','2008-02-22');
insert  into `tabella_nomi`(`Id`,`Nome`,`DataInizio`,`DataFine`) values (2,'Miki','2008-02-23','2008-02-23');
insert  into `tabella_nomi`(`Id`,`Nome`,`DataInizio`,`DataFine`) values (3,'Miki','2008-02-24','2008-02-24');

and the query:

SELECT c.Nome
  FROM tabella_completa AS c
LEFT OUTER
  JOIN tabella_nomi AS n
    ON n.Nome = c.Nome 
   AND n.DataInizio <> '2008-02-25' 
   AND n.DataFIne   <> '2008-02-25' 
 WHERE n.Nome IS NULL
GROUP 
    BY c.Nome ASC

Result:

Jim
Viki

.... and Miki ?

1) Miki ===> whit DataInizio = '2008-02-22' AND DataFine = '2008-02-22'
2) Miki ===> whit DataInizio = '2008-02-23' AND DataFine = '2008-02-23'
3) Miki ===> whit DataInizio = '2008-02-24' AND DataFine = '2008-02-24'

[markrawlingson]

Please be more clear as to your question. What exactly is wrong with the above code? I assume you are getting results. Is it the case that you are returning the WRONG results?

Sincerely,
Mark

[viki1967]

Thanks for your answer.

I extract different dates from today.

In the tabella_nomi fields "DataInizio " and "DataFine" have value 23/02/2008, why not extract this rows ?

Tabella_nomi contains some rows from tabella_complet a.

I have to check with a query that already exists in tabella_nomi a rows corresponding to rows tabella_complet a and exclude this rows.

Please explain...
thanks

Viki1967