Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Need advice on SQL
Message
 
To
17/09/2006 12:19:49
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9 SP1
OS:
Windows XP SP2
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01154440
Message ID:
01154488
Views:
11
Michel,

I would suggest to stop trying to do the whole job with one query. Keep your original query to get the dat with memos with an added filed for firstvideo number like "..., 0 as FirstVideo ...". Make that query READWRITE. Then do a scan loop and fire my suggestion to simply get the first video number for each program. Something like this;
SELECT Program.Numero, Program.Active, Program.Title, Program.Notes_E, Program.Notes_F, ;
       Program.NoMaster, Master.FirstName, Master.LastName, MasterType.Title_E AS MasterType_E, ;
       MasterType.Title_F AS MasterType_F, Video.Url, Video.Numero AS PrimaryKey, INT(0) AS FirstVideo ;
  FROM Program ;
  JOIN Master ON Program.NoMaster=Master.Numero ;
  JOIN MasterType ON Master.NoMasterT=MasterType.Numero ;
  JOIN Video ON Program.Numero=Video.NoProgram ;
  JOIN InvoiceI ON Video.Numero=InvoiceI.NoVideo ;
  JOIN Invoice ON InvoiceI.NoInvoice=Invoice.Numero ;
 WHERE Program.Active AND Invoice.AddUser=1 ;
 ORDER BY Program.Title ;
  INTO CURSOR VidsSold READWRITE


SELECT 0
SELECT Program.Numero AS Numero, MIN(Program.Numero) as FirstVideo ;
  FROM Master ;
  JOIN Program ON Program.NoMaster=Master.Numero ;
 GROUP BY Master.Numero INTO CURSOR FirstVids

SELECT VidsSold
SCAN
   SELECT FirstVids
   LOCATE FOR VidsSold.Numero = FirstVids.Numero
   REPLACE VidsSold.FirstVideo WITH FirstVids.FirstVideo
ENDSCAN

* Now your VidsSold cursor has all of your data (with memos) plus a field named 
* FirstVideo that holds the first video for the given series
The only question here is now many programs are there? If you are dealing with 10's of thousands of programs then this may be too slow, but with hunderds to thousands of programs this approach will be planty fast enough and it is simple with queries that are easily understood and maintained.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform