Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: onytoo
  Where is onytoo?
 Padang
 Indonesia
 onytoo
 To: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 Tags
Subject: RE: JOIN to 2 fields?
Thread ID: 289027 Message ID: 289045 # Views: 32 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 6:06:41 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

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