how to join a table that has two foreign keys linked to the primary key in the other table using c# winform

Question

0.00/5 (No votes)

See more:

hi friends
please kindly give me suggestions, i have been on this issue for long now. i have two tables staffs and students tables on student table i have columns like Name,referringStaffID, BackUpStaffID, Age, and the Staffs table has staffID, Name, and department. i created two foreign keys from the student table which are student_StaffReferringStaff which links the ReferringStaffID to the staffID in the staff table and also another foreign key student_StaffBackupStaff which links the BackupStaffID to staffID in the staff table. my problem now is joining the the student table with the staff table and getting the the referring Staff Name and the Backup Staff Name.please i need your suggestions..
Thanks in advance

Posted 20-Aug-14 5:50am

Ese Ochuko

Add a Solution

Comments

Ese Ochuko 20-Aug-14 11:56am

sorry am using sql server database

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)

ZurdoDev · Accepted Answer · 2014-08-20T05:55:00

You just need to join twice, once for the referring staff and once for the backup staff.

For example:

SQL

SELECT s.Name AS StudentName, r.Name AS ReferringStaffName, b.Name AS BackupStaffName
FROM students s
LEFT JOIN staff r ON s.referringStaffID = r.staffID
LEFT JOIN staff b ON s.BackUpStaffID = b.staffID