Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
newbie2006
Starting Member
7 Posts |
 Posted - 2006-06-26 : 02:42:50
|
| i have a problem with deriving sql statement which involve a unary relationship for one of the table.i have tables with the following attributes: studetn table: student_email (PK), student namesection table: section_no, section_semester (composite primary key)the above has a a M:M relationship. (in my sql database, i will have a student_section table in btw). There is also another evaluation table which records the student_email of student evaluating another student (this will be shown as team_member email) evaluation table: evaluation_id (PK), student_email, teammember_email, studentinput I use the following sql to get the student name for the student who is evaluating and the students being evaluated:select form_id, temp_tb.student_email, temp_tb.student_name, teammember_email, student.student_name as teammember_name, studentinput from (select form_id, student.student_email, student.student_name, teammember_email, studentinputfrom student inner join form_evaluationon evaluation.student_email=student.student_email) as temp_tb inner join studenton temp_tb.teammember_email=student.student_emailAfter that, i need to join to the student_section table. However because a student can be in different sections, so the student section table will have 2 entries with the same studentemail, therefore the above query (when inner join with student_section table)actually retrieve the same result twice.student section:AAA@edu.com | section1 | 2005/2006BBB@edu.com | section1 | 2005/2006AAA@edu.com | section 2 | 2005/2006final query results: evlauation tableformid| studentemail | teammemberemail | studentinput 11111| AAA@edu.com | BBB@edu.com | "very good" 11111| AAA@edu.com | BBB@edu.com | "very good" 11111| BBB @edu.com | AAA@edu.com | "ok" 11111| BBB @edu.com | AAA@edu.com | "ok"how do i resolve the problem? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-26 : 04:22:31
|
The easy way would be to use DISTINCT.select DISTINCT form_id, temp_tb.student_email, temp_tb.student_name, teammember_email, student.student_name as teammember_name, studentinputfrom ( select form_id, student.student_email, student.student_name, teammember_email, studentinput from student inner join form_evaluation on evaluation.student_email = student.student_email ) as temp_tbinner join student on temp_tb.teammember_email = student.student_email Also, you should try to qualify your queries by putting table names before fields for easier reading and speedier selects.Peter LarssonHelsingborg, Sweden |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2006-06-27 : 00:11:27
|
quote:
Originally posted by X002548
OK, what is that?"unary relationship"Is this a college course term?Time to GOOGLEBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
A binary relation is a function which takes in two elements of the domain, and returns true or false (indicating that those two have the desired relation, or that they don't). A unary relation takes in only one input instead of two. You can have ternary and quaternary relations, etc.; however any given relation has a fixed arity (number of inputs). -- KK |
|
|
|
|
|
|
|
|
unary relationship