Multiple object pointer fields

[Chris Sheffield]

Thanks, Charles.  I was kind of afraid that would be the answer.  I  
was hoping there would be a way around that, as I don't really relish  
the thought of restructuring the database to that extent.  But if  
that's all I can do...

Thanks for taking the time to respond.

Chris


On Sep 15, 2005, at 10:25 AM, Charles Yeomans wrote:

>
> 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
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>

------------------------------------------
Chris Sheffield
Read Naturally
The Fluency Company
http://www.readnaturally.com
------------------------------------------