Multiple object pointer fields

[Charles Yeomans]

On Sep 15, 2005, at 11:58 AM, Chris Sheffield wrote:

> Okay, hopefully I can explain this well enough that one of you will be 
> able to give me a hint.  I'm using Revolution and Valentina 1.x, but 
> this is really more of a SQL question than anything I think.
>
> I have three tables:  Teachers, Classes, and Students.  The Students 
> table contains an ObjectPtr field to Teachers and one to Classes.  I 
> am currently using the following query to retrieve the data I need for 
> Teachers, Classes, and Students, and this works perfectly:
>
> SELECT Teachers.RecID, Teachers.FirstName, Teachers.LastName, 
> Classes.ClassName, Classes.Reading, Students.FirstName, 
> Students.LastName, Students.Grade, Teachers.Type FROM Teachers, 
> Classes, Students WHERE Classes.TeacherPtr=*Teachers.RecID AND 
> Students.ClassPtr*=Classes.RecID
>
> Basically what this does is retrieve all the teachers along with any 
> classes and/or students that might be assigned to them.  Now, what I 
> need to do is alter my Students table and add the ability for a 
> student to be assigned to a second teacher and class.  So I've added 
> another ObjectPtr field that points to Teachers and another one that 
> points to Classes.  So what I need is a query similar to the above 
> that will retrieve what I need.
>
> Unfortunately, if I simply add "AND Students.ClassPtr2*=Classes.RecID" 
> to the end of my query, I get an empty set.  I figure there's got to 
> be a way to get what I want.  I'm just not doing the joins correctly 
> or something.  Can someone help?  Let me know if you need more info.  
> I could even send a sample database with data if necessary.


What you want is a many-many relationship between Teachers and 
Students.  For that you need a join table.  This is a table that 
contains two ObjectPtr fields, one pointing to Students and one 
pointing to Teachers.

--------------
Charles Yeomans