Struggling with parenthesis 1

[Kalisto]

I have what started out as a fairly simple (to me anyway) query, that has evolved into a monster.

Now I am struggling with the parenthesis as I am nesting inner joins, so can anyone tell me the rules for nesting correctly?

select b.assigned_to    as b_assigned_to
     , b.bug_id         as b_bug_id
     , b.bug_name       as b_bug_name
     , b.priority_id    as b_priority_id
     , b.project_id     as b_project_id
     , b.status_id      as b_status_id
     , b.test_id        as b_test_id
     , p.project_id     as p_project_id
     , p.project_name   as p_project_name
     , p1.priority_id   as p1_priority_id
     , p1.priority_desc as p1_priority_desc
     , e.employee_id    as e_employee_id
     , e.employee_name  as e_employee_name
     , s.status_id      as s_status_id
     , s.status         as s_status
     , tst.pk           as tst_ID  
  from bugs b
inner
  join (projects p inner join
 (priorities p1 inner join
( employees e [innerjoin
(statuses s inner join 
on b.status_id = s.status_id) 
on b.assigned_to = e.employee_id)
on b.priority_id = p1.priority_id )
on b.project_id = p.project_id) 
left outer
  join TestCases tst 
    on b.test_id = tst.pk;

 

[dhookom]

I do all of this in the design view rather than attempting to understand the ()s and Inners and Outers etc. I did notice [red][innerjoin[/red] in your sql.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

from ((((bugs b
inner join projects p on b.project_id = p.project_id)
inner join priorities p1 on b.priority_id = p1.priority_id)
inner join employees e on b.assigned_to = e.employee_id)
inner join statuses s on b.status_id = s.status_id)
left join TestCases tst on b.test_id = tst.pk;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rac2]

Is it true that the Microsoft software engineers deliberately used the oddly complicated INNER JOIN syntax full of extraneous parentheses with the intention to make SQL look obscurely difficult and thereby promote a dependency on the Design View GUI?

 

[r937]

rac2, no it's not true

if i recall correctly (and that's always an iffy proposition), microsoft didn't write access, it was a purchased product

hey kalisto, nice left outer join, the query looks familiar




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Kalisto]

Thanks all, apologies for not getting back sooner, but I am allowed weekends off for some strange reason

Cheers
K