Multiple object pointer fields

[Chris Sheffield]

So as a follow up to this, I've now created a new table called  
TeachStuInt.  It contains ObjectPtr fields TeacherPtr, ClassPtr, and  
StudentPtr.  Is it bad to have three object pointer fields like  
this?  Would it be better to create two separate tables, one for  
Teachers and Students and one for Classes and Students?  I'm not sure  
if any of this even makes sense to you guys.

Anyway, with my new table I still can't seem to get the query right,  
and maybe it's just because I'm not sure how the SQL should be for  
Valentina.  I keep getting a 616 wrong expression error.  This is  
just a simple test query that I'm running using ViSQL:

SELECT Teachers.LastName, Classes.ClassName, Students.LastName FROM  
Teachers, Classes, Students WHERE  
Students.RecID=TeachStuInt.StudentPtr AND  
TeachStuInt.TeacherPtr=Teachers.RecID

Why does this give me the 616 error?  Can anyone help?  What is  
proper syntax for a query dealing with a many-to-many relationship?

Thanks,
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
------------------------------------------