By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,301 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,301 IT Pros & Developers. It's quick & easy.

Query LEFT OUTER JOIN

100+
P: 263
Hi there.

I just tested this using mysql and it NOT works.

Here are the table structures I used:

Expand|Select|Wrap|Line Numbers
  1. /*Table structure for table `tabella_completa` */
  2.  
  3. CREATE TABLE `tabella_completa` (
  4.   `Nome` varchar(64) default NULL
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  6.  
  7. /*Data for the table `tabella_completa` */
  8.  
  9. insert  into `tabella_completa`(`Nome`) values ('Miki');
  10. insert  into `tabella_completa`(`Nome`) values ('Viki');
  11. insert  into `tabella_completa`(`Nome`) values ('Jim');
  12.  
  13. /*Table structure for table `tabella_nomi` */
  14.  
  15. CREATE TABLE `tabella_nomi` (
  16.   `Id` int(11) NOT NULL auto_increment,
  17.   `Nome` varchar(64) default NULL,
  18.   `DataInizio` varchar(64) default NULL,
  19.   `DataFine` varchar(64) default NULL,
  20.   UNIQUE KEY `Id` (`Id`)
  21. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
  22.  
  23. /*Data for the table `tabella_nomi` */
  24.  
  25. insert  into `tabella_nomi`(`Id`,`Nome`,`DataInizio`,`DataFine`) values (1,'Miki','2008-02-22','2008-02-22');
  26. insert  into `tabella_nomi`(`Id`,`Nome`,`DataInizio`,`DataFine`) values (2,'Miki','2008-02-23','2008-02-23');
  27. insert  into `tabella_nomi`(`Id`,`Nome`,`DataInizio`,`DataFine`) values (3,'Miki','2008-02-24','2008-02-24');
  28.  
and the query:
Expand|Select|Wrap|Line Numbers
  1. SELECT c.Nome
  2.   FROM tabella_completa AS c
  3. LEFT OUTER
  4.   JOIN tabella_nomi AS n
  5.     ON n.Nome = c.Nome 
  6.    AND n.DataInizio <> '2008-02-25' 
  7.    AND n.DataFIne   <> '2008-02-25' 
  8.  WHERE n.Nome IS NULL
  9. GROUP 
  10.     BY c.Nome ASC
  11.  
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'
Feb 25 '08 #1
Share this Question
Share on Google+
2 Replies


markrawlingson
Expert 100+
P: 346
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
Feb 25 '08 #2

100+
P: 263
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_completa.

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

Please explain...
thanks

Viki1967
Feb 26 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.