How to Join this query?

GoMo · #1 (**permalink**) 06-22-06, 10:10

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.

r937 · #2 (**permalink**) 06-22-06, 10:17

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

GoMo · #3 (**permalink**) 06-22-06, 10:36

Thanks, I didn't realise you could AND on joins

Internet Services

Web Development

Digital Marketing

Consumer Tech