Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Muhammad Majid Ali Attari
  Where is Muhammad Majid Ali Attari?
 Hyderabad
 Pakistan
 Muhammad Majid Ali Attari
 To: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 Tags
Subject: RE: I can't make this query
Thread ID: 288235 Message ID: 289358 # Views: 43 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Friday, December 31, 2010 6:24:50 AM         
   


> Hi all, I need some help with this query:
>
>
* master books table
> CREATE CURSOR lib_books (book_id i AUTOINC ,book_name c(10))
> INSERT INTO lib_books (book_name) VALUES ('Airplanes') 
> INSERT INTO lib_books (book_name) VALUES ('Animals') 
> INSERT INTO lib_books (book_name) VALUES ('Cars') 
> * master students table
> CREATE CURSOR students (stud_id i AUTOINC ,stud_name c(10))
> INSERT INTO students (stud_name) VALUES ('John') 
> INSERT INTO students (stud_name) VALUES ('Bill') 
> 
> * transactions table
> CREATE CURSOR lib_trans (id i AUTOINC ,stud_id i,book_id i,ComingBack l,trans_date d,date_recorded t,tmpBookName c(10))
> * books getting rented...
> INSERT INTO lib_trans (stud_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
> 	(1,1,.f.,DATE()-10,DTOT(DATE()-10),'Airplanes')
> INSERT INTO lib_trans (stud_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
> 	(2,3,.f.,DATE()-9,DTOT(DATE()-8),'Animals')
> * books returned by the students...
> INSERT INTO lib_trans (stud_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
> 	(1,1,.t.,DATE()-5,DTOT(DATE()-5),'Airplanes')
> INSERT INTO lib_trans (stud_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
> 	(2,3,.t.,DATE()-4,DTOT(DATE()-3),'Animals')
> 
> 
> *SELECT lt.trans_date,lb.book_name,st.stud_name FROM lib_trans lt ;
> 	JOIN lib_books lb ON lt.book_id = lb.book_id ;
> 	JOIN students st ON lt.stud_id = st.stud_id 
> 	* ????
> 
> * expected result:
> CREATE CURSOR result (book_name c(10),stud_name c(10),dateOut d,dateBack d)
> INSERT INTO result (book_name ,stud_name ,dateOut,dateBack) VALUES ;
> 	('Airplanes','John',DATE()-10,DATE()-5)
> INSERT INTO result (book_name ,stud_name ,dateOut,dateBack) VALUES ;
> 	('Animals','Bill',DATE()-9,DATE()-4)
> BROWSE

>
> How to create a select to get the rented items displayed, with the dates In & Out when the books have been rented and given back? A select that creates a cursor as the one above? The field to separate dateIn and dateOut in the resultset is the field "ComingBack", which is either .F. (renting a item) or .T. (giving a item back).
>
> Regards
> Samir

what if lib_trans table looks like:


create cursor lib_trans (;
id i AUTOINC,;
student i,;
book ,;
date_out d(8),;
date_in d(8);
; && and so on ...
)




Regards,

Muhammad Majid Ali Attari

ENTIRE THREAD

I can't make this query Posted by Samir H. @ 12/18/2010 4:28:32 PM
RE: I can't make this query Posted by tushar @ 12/18/2010 5:03:30 PM
RE: I can't make this query Posted by Samir H. @ 12/18/2010 5:29:22 PM
RE: I can't make this query Posted by Borislav Borissov @ 12/18/2010 5:04:44 PM
RE: I can't make this query Posted by tushar @ 12/18/2010 5:21:26 PM
RE: I can't make this query Posted by Samir H. @ 12/18/2010 5:42:15 PM
RE: I can't make this query Posted by Anders Altberg @ 12/18/2010 7:24:13 PM
RE: I can't make this query Posted by Samir H. @ 12/18/2010 5:25:24 PM
RE: I can't make this query Posted by Anders Altberg @ 12/18/2010 7:43:53 PM
RE: I can't make this query Posted by Samir H. @ 12/28/2010 3:32:52 AM
RE: I can't make this query Posted by Anders Altberg @ 12/30/2010 1:48:06 PM
RE: I can't make this query Posted by Samir H. @ 12/30/2010 1:59:32 PM
RE: I can't make this query Posted by Jun Tangunan @ 12/31/2010 2:52:21 AM
RE: I can't make this query Posted by Samir H. @ 12/31/2010 3:26:06 AM
RE: I can't make this query Posted by Ony Too @ 12/31/2010 4:57:14 AM
RE: I can't make this query Posted by Samir H. @ 12/31/2010 5:11:53 AM
RE: I can't make this query Posted by Ony Too @ 12/31/2010 11:26:02 AM
RE: I can't make this query Posted by Jun Tangunan @ 1/3/2011 4:56:55 AM
RE: I can't make this query Posted by Samir H. @ 1/3/2011 5:11:03 AM
RE: I can't make this query Posted by Muhammad Majid Ali Attari @ 12/31/2010 6:24:50 AM
RE: I can't make this query Posted by Samir H. @ 12/31/2010 6:54:07 AM
RE: I can't make this query Posted by Muhammad Majid Ali Attari @ 1/1/2011 5:45:42 AM
RE: I can't make this query Posted by Mustapha Bihmouten @ 12/31/2010 11:06:04 AM
RE: I can't make this query Posted by Samir H. @ 12/31/2010 4:12:24 PM