Check how many columns contain value in each row of table

Question

0.00/5 (No votes)

See more:

I have these two tables in MSSQL database.

Student Preferences Tables (actual table has 7 preferences)

------------------------------------------------------------------------------
   Student Name |   Preference 1|  Preference 2 | Preference 3  |Group ID
   -----------------------------------------------------------------------
   Student A    |   Student G   |   Student F   |  Student G    |
   Student B    |   Student H   |   Student K   |  Student L    |
   Student C    |   Student I   |   Student A
   Student D    |   Student H   |   Student K
   Student E
   Student F
   Student G
   Student H
   Student I
   Student J
   Student K
   Student L
   -----------------------------------------------------------------------------

Groups Table

|Group ID |
    ---------
   1
   2
   3
   ----------

I am creating groups of student based on preferences(preferred partners/team mates) entered by student. Each group can have 5-8 students.Not all students will enter preferences. To create a group i will check for the students who have entered 4-7 preferences and add them to a group. For example, Student A has entered 4 preferences. Including student A now i have 5 students minimum number required for group. So, i would like to insert in "Groups table" and retrieve that number and insert in "Group ID" column for each student. In cases if student has entered less than 4 preferences, i will have to create a new "group id" and assign to students but i also have to check that 5-8 students per group condition is not breached.

Can someone please guide me on this. How to check for rows where more than 4 preference columns contain data(names)? If students with more than 4 preferences exist, than how can i insert new record in Groups table(Group ID (auto-inc)) and retrieve it back to insert in Student Preference table. How can i check 5-8 students condition is not breaching before inserting group id? I am trying to find out how can these problems be solved (using c# or SQL) in easiest way? I am writing application in C#.

Posted 1-Oct-13 5:27am

Mubshir

Add a Solution

Comments

Kschuler 1-Oct-13 11:55am

With problems like this I think the best way to tackle it is to think about how you would accomplish this if you were doing it manually with paper and pencil. We don't know exactly what kind of rules you want to put into place, for example, if two students both pick each other does that take precedence over one student picking a student who didn't pick them back? In general, I would recommend that you setup the groups in an in-memory DataTable and only when you are done organizing the groups would you write stuff back to the database. If your groups can have 5 to 8 students in them, then how can you be sure how many groups you will actually end up with?

Mubshir 1-Oct-13 12:25pm

Group organizing is not a problem. I will be making changes to the groups manually later because many student will enroll late and many students withdraw from the unit as well. For precedence i am not placing any rules. If any students have entered 4 preferences than, i will add them into a group. The only condition i am trying to fulfill when creating groups is the minimum 5 members, so 4 preferences + Student himself become 5 members, which can be a group.

Kschuler 1-Oct-13 12:30pm

What do you mean by "if any students have entered 4 preferences"? How can they enter 4 preferences if you only have columns for 3? ----Edit --- Okay...I see the comment now that there are actually 7 preference columns....ha. That's what was confusing me.

Mubshir 1-Oct-13 12:42pm

what do you recommend for my table structure? should i normalize it? Something like this:

T_STUDENTS
StudentID
GroupID

T_STUDENT_PREFERENCES
StudentID
Preference

Will it make any easier for me to achieve desired tasks?

Kschuler 1-Oct-13 12:34pm

I still am not sure what you are trying to do. So you mean that if student A prefers B,C,D, and E...you will make that a group, even if Students B,C,D, and E don't prefer each other? Yikes. As for the minimum...How would you do that if you were doing it by hand? Just add more students to the group (who don't currently have a group) until it's met the minimum. Without seeing the code you have, it's hard to be more specific than that.

Mubshir 1-Oct-13 12:39pm

You got it right about making into groups. i wasn't going to implement it this way but i am new to programming, and this is my project due in one week. Do you have any other idea on how should make a group? I have asked this question and searched on the internet but couldn't find any examples on preference based grouping. Thanks

Kschuler 1-Oct-13 15:45pm

does the group table contain anything other than the group id? If not, then why do you even have it?

Mubshir 2-Oct-13 9:27am

No, it only contains group ID. I am using Group ID as Group Name, Group 1, Group 2 etc. So what i want to do is, once i have students who i want to add to group, i create a new Group Id and than in students table i insert same id for each student who are in same group. I am happy to make changes to database to make this application work. Please suggest anything you think is more suitable and i will make changes accordingly. Thanks for your time.

Kschuler 2-Oct-13 9:33am

If I were doing this, I think I'd have one table that contains the student name and the group number. A second table would contain the student name and ONE preference. That table would contain multiple records for each student. That way you can more easily lookup how man preferences a student has, or how many times a student was preferred.

RedDk 1-Oct-13 12:05pm

And another repost

Mubshir 1-Oct-13 12:25pm

How is it a repost?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Accepted Answer · 2013-10-04T07:06:00

You've got it organized incorrectly - this is a relational table problem.

Table 1: Each student entered once with a unique identifier (like an identity field)
Table 2: A reference to the student (col 1) and a preferred partner's identity (col 2). There can be multiple records in this table with the same value in col 1. (I,E. table one supplies a foreign key for table 2)

Now you can easily get counts for any student with Group-by clause on table 2, answering you problem of those that have 'enough' preferences. (You could even count who's most-preferred by counting col 2)

You should insure that a student cannot select themselves as partner OR force this selection automatically, either of these can be done with triggers on table 2.