I recently took over a db designed by another programmer. The DB is designed to track Teachers and their Classes. One Teacher to Many Classes.

Instead of having a field in the Class table for Teacher(id), the original designer added a third table to "cross-reference" this relationship (table is called "xrefTeacherClass")...so we have...

Table: Teacher
Id, Name, Address, School, etc.

Table: Class
Id, ClassName, ClassGrade, etc.

Table: xrefTeacherClass
Id, TeacherID, ClassID

I want to get rid of this "xref" table, so I have added a column to the Class table called "TeacherID", but I have no idea how to SQL the xref values over for all the teachers and classes...

???

-------------------------
Beware of programmers with screwdrivers...

__________________
-------------------------
Beware of programmers with screwdrivers...

hi there...

be aware that the table "XREF..." could be there b/c the original guy though that maybe there could be more than one teacher for a class or to have a history...

anyway i dont know where do you have this but you can do something like this:

(didnt try this dont have any sql on hand..)

select x.teacherid as newteacherid, c.* FROM xrefteacherclass as x, class as c where x.classid = c.id
(get this last query into a new table to replace class)

then you can do the update
update (lastquery) set teacherid = newteacherid
drop newteacherid from (lastquery)

HTH

Gonzalo

Thanks mate!

I worked it out using the following:

UPDATE tblClass SET tblClass.UID = x.TeacherID FROM tblClass As c
LEFT JOIN xrefTeacherClass As x ON x.classid = c.id

And I'll be keeping the xref table there for now. The immediate use of fixing this is for easier report generation...

-------------------------
Beware of programmers with screwdrivers...