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


If you ask me, I would never do it that way. :-)

What will happen if both stud_id and empl_id have values? If you will say that only one will have a value at a time that when either empl_id or stud_id have a value, the other one will have zero; then I will say that that is a waste of space consumed by that unnecessary extra field. I would rather make a single field for the borrower like this:

* 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') 

* one table for both borrower and employee
CREATE CURSOR borrower (borrow_id i AUTOINC ,borrow_name c(10), btype I)
INSERT INTO borrower (borrow_name,btype) VALUES ('John',1) 
INSERT INTO borrower (borrow_name,btype) VALUES ('Bill',1) 
INSERT INTO borrower (borrow_name,btype) VALUES ('Mike',1) 
INSERT INTO borrower (borrow_name,btype) VALUES ('Mary',2) 
INSERT INTO borrower (borrow_name,btype) VALUES ('Lily',2) 
INSERT INTO borrower (borrow_name,btype) VALUES ('Amelie',2) 

CREATE CURSOR borrowtype (btype i autoinc, typedesc c(10))
INSERT INTO borrowtype (typedesc) VALUES ("STUDENT")
INSERT INTO borrowtype (typedesc) VALUES ("EMPLOYEE")


CREATE CURSOR lib_trans (id i AUTOINC ,borrow_id i, book_id i,ComingBack l,trans_date d,date_recorded t,tmpBookName c(10))
* books getting rented...
INSERT INTO lib_trans (borrow_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
	(3,1,.f.,DATE()-30,DTOT(DATE()-30),'Airplanes')
INSERT INTO lib_trans (borrow_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
	(2,3,.f.,DATE()-27,DTOT(DATE()-27),'Cars')
INSERT INTO lib_trans (borrow_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
	(5,2,.f.,DATE()-26,DTOT(DATE()-26),'Animals')
INSERT INTO lib_trans (borrow_id,book_id ,ComingBack ,trans_date,date_recorded,tmpBookName) VALUES ;
	(3,1,.t.,DATE()-26,DTOT(DATE()-25),'Airplanes')


SELECT borrow_name as PersonName, lib_books.book_name, trans_date as dateout FROM lib_trans;
left JOIN borrower ON borrower.borrow_id = lib_trans.borrow_id;
left JOIN lib_books ON lib_books.book_id = lib_trans.book_id;
where !lib_trans.ComingBack


If you later want to distinguish which is employee and which is a mere student, then simply join borrowtype cursor:

SELECT borrow_name as PersonName, lib_books.book_name, trans_date as dateout, borrowtype.typedesc FROM lib_trans;
left JOIN borrower ON borrower.borrow_id = lib_trans.borrow_id;
left JOIN lib_books ON lib_books.book_id = lib_trans.book_id;
left JOIN borrowtype ON borrowtype.btype = borrower.btype;
where !lib_trans.ComingBack


http://www.junblogs.com/
http://weblogs.foxite.com/sandstorm36/default.aspx
http://www.coderisland.com/forum/viewforum.php?f=10

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