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.
 Tags
Subject: JOIN to 2 fields?
Thread ID: 289027 Message ID: 289027 # Views: 37 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 3:29:25 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

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