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: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: I can't make this query
Thread ID: 288235 Message ID: 288991 # Views: 48 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 3:32:52 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
>
>
>
>
SELECT T1.*, T2.transdate FROM Lib_trans T1 JOIN Lib_trans T2 ;
>  ON T1.bookid=T2.bookid AND T1.studentid=T2.studentid
>  WHERE T1.coming_back = .F.  ;
>  AND T2.transdate = (SELECT MIN(trans date) FROM Lib_trans T3;
>  WHERE  T3.trans_date>=T1.transdate AND T3.coming_back=.T.)
> 

>
> It would be simple I think to find the row where the loan is made and enter a return_date.
>
> -Anders


Hi Anders
Sorry for the late reply...
I corrected fieldname-typos in your query as follow
SELECT T1.*, T2.trans_date ;
	FROM Lib_trans T1 ;
		JOIN Lib_trans T2 ;
		ON T1.book_id=T2.book_id AND T1.stud_id=T2.stud_id ;
		;
 	WHERE ;
 		T1.comingback = .F.  ;
 		AND T2.trans_date = (SELECT MIN(trans_date) ;
 		FROM Lib_trans T3 WHERE  T3.trans_date>=T1.trans_date AND T3.comingback=.T.)
but it shows wrong results.
Which actually doesn't matter, I'm more interested in the suggestion you made.
I have thought about doing as you said before starting this thread, but a certain (may be wrong) opinion about transaction tables prevented me from doing it: I think/thought that we should not go back and modify old records in transaction tables. We should always add new records and store actual information in these new records.
What do you think? Is such a opinion completely out-of-fashion (expired :-U) ? In POS I do it that way: no record can be edited, only adding records is allowed...


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