SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query Questions

    Okay so got this database with two tables:

    employee
    -----------------
    ID - primary key
    name - person's name

    project_numbers
    -----------------
    project_num - primary key
    requested_by - ID of person who requested the pn
    approved_by - ID of person who approved the new pn
    leader - ID of project leader


    The question is how do get a query to return the project number along with the person's name who requested, approved and is leading the project. In most cases these will be three different people. How do I do this with one query?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    easy, just join the project_numbers table to the employee table three times --
    Code:
    select project_num
         , r.name     as name_requester
         , a.name     as name_approver
         , l.name     as name_leader
      from project_numbers
    left outer
      join employee r
        on requested_by = r.ID
    left outer
      join employee a
        on approved_by = a.ID
    left outer
      join employee l
        on leader = l.ID
    notice how i use outer joins instead of inner -- that's called "defensive sql" and it guards against situations where one of the foreign keys in the project_numbers table has no match in the employee table, whether this is due to bad integrity (an ID that doesn't exist) or, more realistically, to an optional or missing relationship

    for example, if you have a project that has been requested and approved but no leader has yet been assigned, then the leader FK could be null, and if you were to use inner joins, that project would not be returned by the query

    thus, "defensive sql" returns the results you want (all projects) along with any names it happens to find
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, worked like a charm.


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
  •