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: Jun Tangunan
  Where is Jun Tangunan?
 Cabanatuan
 Philippines
 Jun Tangunan
 Tags
Subject: RE: JOIN to 2 fields?
Thread ID: 289027 Message ID: 289059 # Views: 27 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 8:00:42 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:


Hello Jun
I forgot to mention that the students and employees tables already exist, and they are necessary for my school app. So creating a borrower table is not an option.
You're right: only 1 field stud_id OR empl_id will have a value, the other field gets then a zero. A book is either borrowed by an employee or a student.
The library is just an additional module in the school app.
How would you do it then?




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



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