Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Need advice on SQL
Message
 
 
To
16/09/2006 21:30:01
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:
01154443
Views:
9
This message has been marked as the solution to the initial question of the thread.
Try
...
 LEFT JOIN InvoiceI ON Video.Numero=InvoiceI.NoVideo
 LEFT JOIN Invoice ON InvoiceI.NoInvoice=Invoice.Numero
                         AND Invoice.AddUser=1
 WHERE Program.Active 
 	AND (InvoiceI.NoVideo IS NOT NULL 
 		OR Video.Numero = ( 
 			SELECT MIN(Video.Numero) FROM Program p2 
 				JOIN Video v2 ON P2.Numero=V2.NoProgram
 				WHERE p2.Numero = Program.Numero)
             )
>From the following SQL, I obtain some video information in regards to the video purchased by a user, when the video is part of a program. A program contains multiple videos.
>
>
>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;
> FROM Program;
> INNER JOIN Master ON Program.NoMaster=Master.Numero;
> INNER JOIN MasterType ON Master.NoMasterT=MasterType.Numero;
> INNER JOIN Video ON Program.Numero=Video.NoProgram;
> INNER JOIN InvoiceI ON Video.Numero=InvoiceI.NoVideo;
> INNER JOIN Invoice ON InvoiceI.NoInvoice=Invoice.Numero;
> WHERE Program.Active AND Invoice.AddUser=1;
> ORDER BY Program.Title
>
>
>This works well. For each purchased video part of a program, I obtain the program information, the related master information as well as the video information.
>
>What I need to do from this SQL is to get the first video of the program. So, if a program contains video 11, 13, 33, 45 and the user purchases video 13 and 33, I need to obtain 11 because this video ID was created the first one in the program. Based on that SQL result, what can I add to achieve that?
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform