May 23, 2008 at 7:54 am
I have a table named Courses which contains course information as well as a column named No_Units, which gives the number of units this course should have. I have another table named Units that contains units for all of the courses. The Unit table does not always contain the amount of units for the courses that the Course table No_units shows. I would like to create a stored procedure that I can call that will automatically create the missing units in the Unit table. I basically just need to add entries into the UNITS table with the courseid until the count for the courseid = No_units in the COURSE table.
Thanks in advance
May 23, 2008 at 8:02 am
Not really sure what you are asking. It would help if you would provide the DDL for the tables, sample data (in the form of insert statements) for each table, and the expected results of the query based on the sample data.
😎
May 23, 2008 at 8:56 am
dp (5/23/2008)
I have a table named Courses which contains course information as well as a column named No_Units, which gives the number of units this course should have. I have another table named Units that contains units for all of the courses. The Unit table does not always contain the amount of units for the courses that the Course table No_units shows. I would like to create a stored procedure that I can call that will automatically create the missing units in the Unit table. I basically just need to add entries into the UNITS table with the courseid until the count for the courseid = No_units in the COURSE table.
This sounds like homework.
To get accurate help, you should follow these guidelines[/url].
But, to take a swipe at the problem... I'm confused by the question. Are you trying to get the No_Units column to match the actual number of rows in the Units table or are you trying to insert the number of rows in the Units table to make it match the value in No_Units?
By the way, it doesn't make sense, under normal circumstances, to have a value in one table that is derived from the count of another table. Usually this is derived data (yes, a gazillion exceptions come to mind, but until the exception is defined, the usual case should be approached).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 23, 2008 at 9:41 am
Sorry for the confusion It was hard trying to make the question make any sense. I am trying to make the number of rows in the UNits table match the No_Units value from the Courses table.
The reason that I am doing this is because the Units table data is displayed in a datagrid based on a course that is selected from a dropdown. This will be used by professors to select there course and view the corresponding units for it. If the number of units for the selected course does not match the No_units from the Course table I want to add the extra units so the professors will see basically blank units in the datagrid so they will know that they need to edit the information for the newly added units.
Hope this didn't just get more confusing
May 23, 2008 at 10:30 am
Before I try to solve that, you might want to rethink the approach. If you fill out the rows for the user, even though there's no data, you either go with a bunch of default information or you have make all the columns except the PK and the FK to Course table allow nulls. Which means lots of empty data in the database.
Instead of populating the database with data that doesn't carry meaning, just show the rows as blank when there aren't enough in the database on the app. Showing nothing shouldn't require adding rows filled with nothing to the database. Then, when the user fills out the data, you save what they filled out, but if they haven't filled out anything, you don't save anything.
Could that work or are you pretty much committed to the blank rows approach?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 23, 2008 at 12:19 pm
If I understand what you are suggesting I could see that working. They just want the professors to see the number of units that they should have for the selected course. So if they see rows like you suggested they would know to fill them out and save them. How would you suggest doing this?
May 23, 2008 at 12:26 pm
I'm not a hard-core programmer, so forgive me if I say something more stupid than I usually do...
You'll get the number of units that should be displayed and you'll get the number of units that you have. Presumably, the number you have will always be equal to or less than the number that should be there. (remember, I'm not writing C# code every day any more, so I may get term or two wrong) Let's say you're loading a grid from a data set. The number of rows in the data set is 5, but it should be 10. Add five new rows to the data set. Just make sure they're not marked as dirty until the user actually updates the data.
Again, if I'm way off on this, ignore everything I just said outside the database sphere.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply