19 users online (0 members and 19 guests)  


  Results 1 to 6 of 6

Related

  1. Select Query    Forum: Databases
    Replies: 2
  2. Replies: 1
  3. SELECT Choose 1 but send 2 values?    Forum: HTML Forum
    Replies: 0
  4. Replies: 2
  1. #1
    hermoine's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Mar 2007
    Posts
    32

    Lightbulb retreiving values from sql using a complicated select query.

    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...
    Last edited by hermoine; 03-16-2007 at 05:28 AM. Reason: to mention Q for sql query

  2. #2
    vinyl-junkie's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    721

    Re: retreiving values from sql using a complicated select query.

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

    Code:
    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!
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more

  3. #3
    hermoine's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Mar 2007
    Posts
    32

    Exclamation Re: retreiving values from sql using a complicated select query.

    Code:
     
    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.

  4. #4
    vinyl-junkie's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    721

    Re: retreiving values from sql using a complicated select query.

    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:

    Code:
    (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.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more

  5. #5
    hermoine's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Mar 2007
    Posts
    32

    Talking Re: retreiving values from sql using a complicated select query.

    thanks a lot vinyl-junkie,
    its some what what i need. but i got the idea of inner join.
    Code:
     
    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.

  6. #6
    vinyl-junkie's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    721

    Re: retreiving values from sql using a complicated select query.

    I'm glad I could help.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more