SQL Query Questions

[CyberFuture]

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

[r937]

easy, just join the project_numbers table to the employee table three times --

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

[CyberFuture]

Thanks, worked like a charm.