Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Christian Tabligan
  Where is Christian Tabligan?
 Bacolod City, Neg. Occ.
 Philippines
 Christian Tabligan
 To: bryan wetton
  Where is bryan wetton?
 Morphett Vale
 Australia
 bryan wetton
 Tags
Subject: RE: SQL Help
Thread ID: 228861 Message ID: 228866 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Friday, May 22, 2009 10:27:11 AM         
   


> I have a problem with an SQL statement - I know that what I have done (using the plain list of tables )is not correct as I get more than one copy of certain records. I need help to JOIN correctly. Can someone suggest the modifications to this code below the FROM statement( I have tried but cannot get it right).
>
> Select A.&myflagfield As flagval,;
> G.Edate,;
> G.Recno As MyRecno,;
> G.EFoot As Myextra,;
> G.Etype As ETYPENUM,;
> G.placenum As placenum,;
> A.ref_id As tmg_ID,;
> A.dsid As dsid,;
> T.etypename As etypename;
> FROM ;
> TEMP_A As A,TEMP_N As N,TEMP_T As T,TEMP_G As G;
> where T.ETYPENUM= G.Etype;
> and N.Nper=G.per1;
> and N.primary; && <-----What's this? Is this a Logical?
> and A.per_no=N.Nper;
> AND A.ref_id > 0;
> Into Table mycursor1
>
> Thanks
> -Bryan

Use WHERE..IN Clause...

SELECT A.&myflagfield As flagval,;
      G.Edate,;
      G.Recno As MyRecno,;
      G.EFoot As Myextra,;
      G.Etype As ETYPENUM,;
      G.placenum As placenum,;
      A.ref_id As tmg_ID,;
      A.dsid As dsid,;
      T.etypename As etypename;
FROM ;
      TEMP_A As A,TEMP_N As N,TEMP_T As T,TEMP_G As G;
WHERE T.ETYPENUM IN (SELECT Etype from G);
      AND N.Nper IN (SELECT per1 FROM G);
      AND N.primary; && <-----What's this? Is this a Logical? 
      AND A.per_no IN (SELECT Nper FROM N);
      AND A.ref_id > 0;
INTO CURSOR mycursor1


Best Regards,

CriZ (,")

"Happiness is real when shared."

ENTIRE THREAD

SQL Help Posted by bryan wetton @ 5/22/2009 9:11:07 AM
RE: SQL Help Posted by Christian Tabligan @ 5/22/2009 10:27:11 AM
RE: SQL Help Posted by bryan wetton @ 5/22/2009 11:59:29 AM
RE: SQL Help Posted by Christian Tabligan @ 5/25/2009 1:40:05 AM
RE: SQL Help Posted by Anders Altberg @ 5/22/2009 12:01:25 PM