retreiving values from sql using a complicated select query.

[hermoine]

am having a problem with sql.

i have a table A. in that i have three columns a,student1,student2. it is the primary key of table A and student id of two different students. i have another table student which includes the student id and student name. now i have to display the following:

Table A's primary key, corresponding student1's name and corresponding students2's name.

if we use select * from A we get only the student ids. i need them to be replaced by their names.

can any one help me. please...

[vinyl-junkie]

Sorry, I'm a bit confused about your table structure. Could you list table names and data elements, something along these lines:

Table 1
Field 1
Field 2

Table 2
Field 1
Field 2

That way, I think, will be a little easier to help you. Thanks!

[hermoine]

 
Table 1 - Student
student id - primary key
student name
Table 2
id - primary key
student 1
student 2

student 1 and student2 of table 2 is the foriegn keys of student table. ie they contain only student ids and not names.
I want to display the table 2 in my form. But the form should contain only the names of the students but not their ids. how can i link them. since there are columns pointing to the same primary key simple joins are not working.

[vinyl-junkie]

Let me see if I understand this correctly. Student 1 is an id which links to Student id in the Student table. Student 2 is also an id which links to Student id in the Student table. Do I have that right? If so, here's how to get all the student names:

(select * from Student
inner join Student on
student id = student 1)
union
(select * from Student
inner join Student on
student id = student 2)

If that doesn't work or you think I still don't understand your table structure, give me an example of data for the two tables and the results you're trying to achieve. Thanks.

[hermoine]

thanks a lot vinyl-junkie,
its some what what i need. but i got the idea of inner join.

 
select a.a, st1.studentname, st2.studentname from a inner join students st1 on a.student1 = st1.studentid inner join students st2 on a.student2 = st2.studentid

i tried this following code and i got the result.
once again thanks for taking pains to give me a solution.

[vinyl-junkie]

I'm glad I could help.