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

Thanks Muhammad, that is more or less what Anders suggested to me.

Regards
Samir

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