Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Kulwant Singh
  Where is Kulwant Singh?
 Shimla
 India
 Kulwant Singh
 Tags
Subject: RE: sql join statement
Thread ID: 228177 Message ID: 228320 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Monday, May 18, 2009 10:15:46 AM         
   


> >
> > There are two types of search conditions, the ON clauses and the WHERE clause.
> > The JOIN is an outer left join with Clist being the LEFT side part.
> > However, a LEFT|RIGHT OUTER JOIN exception only applies to the ON clause; a WHERE clause has absolute priority and applies unconditionally even when in an outer join query.
> >
> > By moving some search conditions from the WHERE clause to the ON clause you can change the behavior of the query.
> >
> > Actually the condition "narrat.vno = entry.vno" is already in an LEFT OUTER JOIN .. ON clause, not in the WHERE clause, as you by mistake stated. You didn't include the condition "clist.cclass = 'B'" anywhere at all.
> > Also search condiontions like "entry.code_no = 3" can go in an ON clause.
> >
> > Given the data you posted, please show by example exactly what rows you want to count and what the SUM(Entry.amount) for each group is expected to be.
> >
> > -Anders
>
> Dear Mr. Anders and Mr. Tushar,
>
> Thank you for your time.
>
> The following is giving me the required result
>
>
SELECT acno , sect from FROM CLIST where CLIST.SECT = 'B' INTO TABLE TEMP
> 
> SELECT entry.*;
> FROM entry, narrat ;
> WHERE entry.vno = narrat.vno AND ;
> narrat.edate >= DATE(gCURRENTYEAR,03,01) AND ;
> narrat.edate < THISFORM.TEXT1.VALUE AND ;
> entry.code_no = 3;
> INTO CURSOR temp1
> 
> 
> SELECT CLIST.acno, NVL(SUM(temp1.amount),000000) AS RECPT ;
> FROM CLIST LEFT JOIN temp1 ON CLIST.acno = temp1.acno ;
> GROUP BY 1 INTO CURSOR RECPTS

>
>
> Can you help combine the last two SQL Statements into ONE?
>
> Thanks!
> kulwant

 SELECT CLIST.acno, NVL(SUM(temp1.amount),000000) AS RECPT ;
 FROM CLIST LEFT JOIN ( ;
 SELECT entry.*;
 FROM entry, narrat ;
 WHERE entry.vno = narrat.vno AND ;
 narrat.edate >= DATE(gCURRENTYEAR,03,01) AND ;
 narrat.edate < THISFORM.TEXT1.VALUE ;
 AND entry.code_no = 3 );
 AS temp1 ;
 ON CLIST.acno = temp1.acno ;
 GROUP BY 1 INTO CURSOR RECPTS


-Anders

ENTIRE THREAD

sql join statement Posted by kulwant singh @ 5/16/2009 8:34:27 AM
RE: sql join statement Posted by tushar @ 5/16/2009 8:58:51 AM
RE: sql join statement Posted by Anders Altberg @ 5/16/2009 8:38:34 PM
RE: sql join statement Posted by kulwant singh @ 5/18/2009 5:58:01 AM
RE: sql join statement Posted by M. Akram Bhatti @ 5/18/2009 7:24:08 AM
RE: sql join statement Posted by kulwant singh @ 5/19/2009 7:04:19 AM
RE: sql join statement Posted by Anders Altberg @ 5/18/2009 10:15:46 AM
RE: sql join statement Posted by kulwant singh @ 5/19/2009 7:03:24 AM