SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2006
    Location
    Cardiff, Wales. UK
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using same fields from same table twice

    I have 2 tables: First, 'user', with the fields username, forename, surname and email, role - contains all the users in the system.
    Second, 'tutorGroups', with the fields studentID and tutorID - the point being that it says which students have which tutor. One tutor may have many students. The studentID and tutorID fields refer to the username field in the users table.

    However, I need to list each student, their email and their tutor's email, but how can I query the same table for the same field twice? Is this possible?

    Any help would be much appreciated, I've been trying to work it using alias but getting all confused.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select s.forename
         , s.surname
         , s.email
         , t.forename as tutor_forename
         , t.surname as tutor_surname
         , t.email as tutor_email
      from tutorGroups g
      join user s
        on s.id = g.studentid
      join user t
        on t.id = g.tutorid
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2006
    Location
    Cardiff, Wales. UK
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much! I would never have worked that out myself.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •