how to bring the names from another table

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to bring the names from another table

Author

Topic

tokue
Starting Member

16 Posts

Posted - 2010-07-29 : 22:10:41

I think my problem is easy but I don't know how to solve it, so please help me as fast as you can.

I have to tables, EXAM, STUDENT ... EXAM has the following columns:
Id, date, bestScore(int), secondBestScore(int)

the STUDENTS table has these columns:
id, studentName

the bestScore and secondBestScore in table EXAM are numbers, these numbers are the id in the second table

what I want is to bring the EXAM table but instead of having numbers in (bestScore and secondBestScore) I want the students names, the table which I want must look like this :
exam, name_bestScore, name_secondBestScore

I am waiting your answer please.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-29 : 22:28:11

INNER JOIN to the STUDENT table twice use table alias on it


SELECT *
FROM   EXAM e
       INNER JOIN STUDENTS f ON e.bestScore       = f.id
       INNER JOIN STUDENTS s ON e.secondbestScore = s.id

KH
[spoiler]Time is always against us[/spoiler]

tokue
Starting Member

16 Posts

Posted - 2010-07-29 : 22:58:31

dear khtan, thank you from the bottom of my heart, it works and I am so happy now

tokue
Starting Member

16 Posts

Posted - 2010-07-29 : 23:24:55

Mr. khtan, wait please...
I want to deal with my table in code using C sharp and ADO net, so how can know which column, do I need to use f.bestScore?? I doesn't seem to work for me, I am really confused...

and something else, what if I don't want to bring all columns, how can I do that? I don't want to use *

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-29 : 23:34:41

you need to specify the required column name in the SELECT list
example


SELECT name_bestScore        = f.studentName,
       name_secondBestScore  = s.studentName

KH
[spoiler]Time is always against us[/spoiler]

tokue
Starting Member

16 Posts

Posted - 2010-07-29 : 23:52:19

finally I got it.... again thanks a lot, my small program works now better than google

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources