Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Need advice on SQL
Message
From
17/09/2006 13:06:14
 
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:
01154480
Views:
9
Ok, I finally got it. I had to adjust the memo field syntax as this was not able to be aggregated. I have to say it was one of the most difficult SQL I ever had to deal with. Thanks to you, Sergey and Fabio for all the help.

Here is the final SQL:
SELECT Program.Numero,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,Program4.VideoNumero,Video.Url;
 FROM Program;
 INNER JOIN;
  (SELECT Program2.Numero,MIN(Video2.Numero) AS VideoNumero FROM Program AS Program2 ;
  INNER JOIN Video AS Video2 ON Program2.Numero=Video2.NoProgram;
  WHERE Program2.Active AND Program2.Numero IN;
   (SELECT Program3.Numero FROM Program Program3;
   INNER JOIN Video AS Video3 ON Program3.Numero=Video3.NoProgram;
   INNER JOIN InvoiceI ON Video3.Numero=InvoiceI.NoVideo;
   INNER JOIN Invoice ON InvoiceI.NoInvoice=Invoice.Numero AND Invoice.AddUser=?);
  GROUP BY Program2.Numero) Program4 ON Program4.Numero=Program.Numero;
 INNER JOIN Master ON Program.NoMaster=Master.Numero;
 INNER JOIN MasterType ON Master.NoMasterT=MasterType.Numero;
 INNER JOIN Video ON Program4.VideoNumero=Video.Numero;
 ORDER BY 2
Michel Fournier
Level Extreme Inc.
Designer, architect, owner of the Level Extreme Platform
Subscribe to the site at https://www.levelextreme.com/Home/DataEntry?Activator=55&NoStore=303
Subscription benefits https://www.levelextreme.com/Home/ViewPage?Activator=7&ID=52
Previous
Reply
Map
View

Click here to load this message in the networking platform