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: onytoo
  Where is onytoo?
 Padang
 Indonesia
 onytoo
 Tags
Subject: RE: JOIN to 2 fields?
Thread ID: 289027 Message ID: 289073 # Views: 27 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 8:51:19 AM         
   


> > Hi experts
> > In a library transaction table, people renting the books can come from 2 different tables, students.dbf or employees.dbf. I am using 2 foreign key fields to identify the person:
> > - 1 field stores the student's ID
> > - 1 field stores the employees's ID
> > IDs are integer (not GUID), so I can't use 1 FK field only: I must have 2 fields.
> > To retrieve the person's name in queries, I use NVL(stud_name,empl_name).
> >
> > Is that how you experts would do it as well? Is there a better approach (may be using a logical field to distinguish students from employees) ?
> >
> >
* sample data...
> > * books
> > 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') 
> > * students
> > 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') 
> > INSERT INTO students (stud_name) VALUES ('Mike') 
> > * employees 
> > CREATE CURSOR employees (empl_id i AUTOINC ,empl_name c(10))
> > INSERT INTO employees (empl_name) VALUES ('Mary') 
> > INSERT INTO employees (empl_name) VALUES ('Lily') 
> > INSERT INTO employees (empl_name) VALUES ('Amelie') 
> > 
> > CREATE CURSOR lib_trans (id i AUTOINC ,stud_id i,empl_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,empl_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
> > 	(3,0,1,.f.,DATE()-30,DTOT(DATE()-30),'Airplanes')
> > INSERT INTO lib_trans (stud_id,empl_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
> > 	(0,2,3,.f.,DATE()-27,DTOT(DATE()-27),'Cars')
> > INSERT INTO lib_trans (stud_id,empl_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
> > 	(2,0,2,.f.,DATE()-26,DTOT(DATE()-26),'Animals')
> > INSERT INTO lib_trans (stud_id,empl_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
> > 	(3,0,1,.t.,DATE()-26,DTOT(DATE()-25),'Airplanes')
> > 
> > 
> > select NVL(st.stud_name,em.empl_name) as personName,lb.book_name,trans_date as dateout,;
> > (SELECT MIN(trans_date) FROM lib_trans l ;
> > 	WHERE l.trans_date>lib_trans.trans_date ;
> > 	AND l.book_id=lib_trans.book_id ;
> > 	AND l.stud_id=lib_trans.stud_id) as dateback ;
> > FROM lib_trans ;
> > JOIN lib_books lb ON lib_trans.book_id = lb.book_id ;
> > LEFT JOIN students st ON lib_trans.stud_id = st.stud_id ;
> > LEFT JOIN employees em ON lib_trans.empl_id = em.empl_id ;
> > where !lib_trans.ComingBack

> >
> > Regards
> > Samir
>
> Hi Samir,
>
> You only can check for student on this command:
>
> (SELECT MIN(trans_date) FROM lib_trans l ;
> 	WHERE l.trans_date>lib_trans.trans_date ;
> 	AND l.book_id=lib_trans.book_id ;
> 	AND l.stud_id=lib_trans.stud_id) as dateback ;
> 

>
> Try this one
>
> SELECT s.stud_name as personname,b.book_name,trans_date as dateout, ;
> 	(SELECT MIN(trans_date) FROM lib_trans l ;
> 		WHERE l.trans_date>lib_trans.trans_date ;
> 		AND l.book_id=lib_trans.book_id ;
> 		AND l.stud_id=lib_trans.stud_id) as dateback ;
> 	FROM lib_trans ;
> 	LEFT JOIN students s ON lib_trans.stud_id=s.stud_id ;
> 	LEFT JOIN lib_books b ON lib_trans.book_id=b.book_id ;
> 	WHERE !EMPTY(lib_trans.stud_id) AND !lib_trans.ComingBack ;
> 	UNION SELECT e.empl_name,b.book_name,trans_date, ;
> 		(SELECT MIN(trans_date) FROM lib_trans l ;
> 			WHERE l.trans_date>lib_trans.trans_date ;
> 			AND l.book_id=lib_trans.book_id ;
> 			AND l.empl_id=lib_trans.empl_id) ;
> 		FROM lib_trans ;
> 		LEFT JOIN employees e ON lib_trans.empl_id=e.empl_id ;
> 		LEFT JOIN lib_books b ON lib_trans.book_id=b.book_id ;
> 		WHERE !EMPTY(lib_trans.empl_id) AND !lib_trans.ComingBack ;
> 	ORDER BY 3
> 

>
> Regards,
> Onytoo

You're right. I copy-pasted the code samples from one of my recent threads and forgot to correct the SQL. It probably doesn't matter anymore, since I plan to add a field and put the "DateBack" in the same record where the book has been rented. That will make my queries easier, more readable and that is probably anyway the better solution.
Thanks for your reply.

Regards
Samir

ENTIRE THREAD

JOIN to 2 fields? Posted by Samir H. @ 12/28/2010 3:29:25 AM
RE: JOIN to 2 fields? Posted by Jun Tangunan @ 12/28/2010 5:40:29 AM
RE: JOIN to 2 fields? Posted by Samir H. @ 12/28/2010 8:00:42 AM
RE: JOIN to 2 fields? Posted by Jun Tangunan @ 12/28/2010 8:18:38 AM
RE: JOIN to 2 fields? Posted by Ony Too @ 12/28/2010 6:06:41 AM
RE: JOIN to 2 fields? Posted by Samir H. @ 12/28/2010 8:51:19 AM