Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Unanswered: How to Join this query?

    I'm trying to join two tables:

    Code:
    tbl_role                         tbl_project_role
    id  role        rate-default     id  project-id  role-id  rate-override
    1   Manager     100.00           1   4           1        80.00
    2   Programmer  40.00            2   5           1        90.00
    3   Designer    50.00            3   4           2        45.00
                                     4   6           3        45.00
    So now I'm trying to find the rates for all the roles for any given project, say project #4. I would like the results in the following format.

    Code:
    role-id  role        rate-default  project-role-id  project-id  rate-override
    1        Manager     100.00        1                4           80.00
    2        Programmer  40.00         3                4           45.00
    3        Designer    50.00         NULL             NULL        NULL
    I'm just not sure how to construct this query, any ideas?

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select r.id    as role_id  
         , r.role        
         , r.rate_default
         , pr.id   as project_role_id
         , pr.project_id
         , pr.rate_override
      from tbl_role as r
    left outer
      join tbl_project_role as pr
        on pr.role_id = r.id
       and pr.project_id = 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    Thanks, I didn't realise you could AND on joins

Posting Permissions

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