2 tables dependent on the other 2

[Lladros]

Hello All!

Is this possible and if so, how?

I have 2 tables a Lessons table and an Assignments table. Each Lesson has multiple Assignments. The previous years' Lessons and Assignments need to be copied with the new school year of 2009. I was successfully able to copy the previous year's information with the school year of 2009. However, on the Assignment side, after I copied 2008 info., it now contains the lesson id of 2008 which is a foreign key in my Assignments table(assign_less_id).

I have a select statement that looks like this:

SELECT * FROM LESSONS AS a INNER JOIN ASSIGNMENTS AS b ON a.lesson_schlyr=b.assignment_schlyr WHERE a.lesson_schlyr = '2009' AND b.assignment_schlyr = '2009' ORDER BY a.lesson_id, b.assign_less_id

I need that each time the old lesson id on the Assignments table (assign_less_id) changes to assign the next new Lesson Id for 2009.

Oh, I don't know if I am explaining myself clearly enough.

Please Help!

Lladros

 

[bborissov]

What type is LESSONS.assignment_schlyr?
Why not generate new PKs in LESSONS and than use that keys when you copy Assignment records?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Lladros]

Thanks for your quick response.

Ok. I am not sure what you are suggesting but in the Lessons table I do have a PK of lesson_id. When I copied the old school year to the new school year, this is all within the same table. I also have a PK in the Assignments table of assign_id and a FK that relates to the Lessons table of assign_less_id.

I need the assignments to have the correct assign_less_id that it pretains to. I have multiple assignments that belong to one Lesson.

Lladros

 

[jbenson001]

Show some sample data in each table and what the results are that your getting.

 

[Lladros]

This is my lessons table:
less_id lesson_title lesson_desc inst schlyr
61 Lesson One Exploring Careers 1 2008
132 Lesson Two Self-Awareness 1 2008
133 Lesson Three Goal Setting 1 2008
134 Lesson Four Understanding 1 2008
135 Lesson Five Workplace Skills 1 2008
136 Lesson Six Career 1 2008
617 Lesson One Exploring Careers 1 2009
618 Lesson Two Self-Awareness 1 2009
619 Lesson Three Goal Setting 1 2009
620 Lesson Four Understanding 1 2009
621 Lesson Five Workplace Skills 1 2009
622 Lesson Six Career 1 2009

This is my assignments table:
assign assign_title assign_desc assign schlyr
_id _less_id

1 Assign 1 Right Match 61 2008
18 Assign 2 Career Clusters 61 2008
19 Assign 3 Wages and Salaries 61 2008
20 Assign 4 Budgets 61 2008
21 Assign 5 Vocational/Technical 61 2008
22 Assign 6 Colleges/Universities61 2008
23 Assign 7 Using the Internet 61 2008
24 Assign 1 Personal Value System132 2008
25 Assign 2 Personality Strengths132 2008
26 Assign 3 Career Inventory 132 2008
50 Assign 3B Career Invetory-2 132 2008
51 Assign 3C Career Inventory-3 132 2008
27 Assign 4 Personal Abilities 132 2008
28 Assign 1 Importance of Goals 133 2008
29 Assign 2 Realistic Goals 133 2008
30 Assign 3 Roadblocks/Barriers 133 2008
31 Assign 4 Now, It's Your Turn! 133 2008
32 Assign 5 Decision Making 133 2008
33 Assign 6 Decision Trees 133 2008
34 Assign 1 Why Do People Work? 134 2008
35 Assign 2 Nothing Stays Same 134 2008
36 Assign 3 Lifelong Learning 134 2008
37 Assign 4 Equal Rights 134 2008
38 Assign 5 Self- Advocacy 134 2008
39 Assign 1 Ethical Decisions 135 2008
40 Assign 2 Where Do I Fit? 135 2008
41 Assign 3 The Job Application 135 2008
42 Assign 4 The Interview 135 2008
43 Assign 1 Life After HS 136 2008
44 Assign 2 Your Next Step 136 2008
45 Assign 3 Financial Assistance 136 2008
46 Assign 4 Stereotyping 136 2008
47 Assign 5 Diversity 136 2008
48 Assign 6 Planning Ahead 136 2008
49 Assign 7 Selecting a Major 136 2008
1874 Assign 1 Right Match 61 2009
1875 Assign 2 Career Clusters 61 2009
1876 Assign 3 Wages and Salaries 61 2009
1877 Assign 4 Budgets 61 2009
1878 Assign 5 Vocational/Technical 61 2009
1879 Assign 6 Colleges/Universities61 2009
1880 Assign 7 Using the Internet 61 2009
1881 Assign 1 Personal Value System132 2009
1882 Assign 2 Personality Strengths132 2009
1883 Assign 3 Career Inventory 132 2009
1907 Assign 3B Career Invetory-2 132 2009
1908 Assign 3C Career Inventory-3 132 2009
1884 Assign 4 Personal Abilities 132 2009
1885 Assign 1 Importance of Goals 133 2009
1886 Assign 2 Realistic Goals 133 2009
1887 Assign 3 Roadblocks/Barriers 133 2009
1888 Assign 4 Now, It's Your Turn! 133 2009
1889 Assign 5 Decision Making 133 2009
1890 Assign 6 Decision Trees 133 2009
1891 Assign 1 Why Do People Work? 134 2009
1892 Assign 2 Nothing Stays Same 134 2009
1893 Assign 3 Lifelong Learning 134 2009
1894 Assign 4 Equal Rights 134 2009
1895 Assign 5 Self- Advocacy 134 2009
1896 Assign 1 Ethical Decisions 135 2009
1897 Assign 2 Where Do I Fit? 135 2009
1898 Assign 3 The Job Application 135 2009
1899 Assign 4 The Interview 135 2009
1900 Assign 1 Life After HS 136 2009
1901 Assign 2 Your Next Step? 136 2009
1902 Assign 3 Financial Assistance 136 2009
1903 Assign 4 Stereotyping 136 2009
1904 Assign 5 Diversity 136 2009
1905 Assign 6 Planning Ahead 136 2009
1906 Assign 7 Selecting a Major 136 2009

As you can see, I have copied the previous year's info. to the same table with the new school year. The one's with the new school year, note that the assign_less_id is the previous year's Lesson_id. I need those to now be the new school year's Lesson_ids.

Lladros

 

[PatriciaObreja]

UPDATE Assignements
SET assign_less_id = 
(SELECT lesson_id FROM Lessons WHERE lesson_desc = 
 (SELECT lesson_desc WHERE lesson_id = assign_less_id)
 and schlyr = '2009'
)
WHERE a.lesson_schlyr = '2009'

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[bborissov]

OK I assume less_id field is Int IDENTITY field. So WHEN you copy these records:
(I really hate to join tables with some description, I am never sure that they would be unique)

ALTER TABLE Lesson ADD OldId int DEFAULT(0)
GO
UPDATE Lessons Set OldId = Less_Id WHERE schlyr = 2008
INSERT INTO Lessons (lesson_title, lesson_desc, inst, schlyr, OldId)
SELECT (lesson_title, lesson_desc, inst, 2009, OldId)
       FROM Lessons WHERE schlyr = 2008

INSERT INTO ASSIGNMENTS
(assign_title, assign_desc, assign_less_id, schlyr)
SELECT assign_title, assign_desc, Lessons.less_id , 2009
       FROM ASSIGNMENTS
       INNER JOIN Lessons ON ASSIGNMENTS.assign_less_id = Lessons.OldId AND Lessons.schlyr = 2009
       WHERE ASSIGNMENTS.schlyr = 2008
ALTER TABLE Lesson DROP COLUMN OldId int DEFAULT(0)

NOT TESTED AT ALL
If you think that lesson_desc is unique than PatriciaObreja suggestion should work for you.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP