CODEWITHSUNDEEP
sql-server-2008joininner-join
camelbrush
camelbrush

Reputation: 236

Why is this inner join SQL block failing?

Receiving incorrect syntax near as when running this inner join. Need to determine why and cannot. Please point out where this goes wrong:

Relevant SQL Code

from 

(   
    (
        (dbo.task inner join dbo.projwbs on dbo.task.proj_id=dbo.projwbs.wbs_id) as tmptb1
    inner join
        (dbo.task inner join dbo.project on dbo.task.proj_id=dbo.project.proj_id) as tmptb2
        on dbo.tmptb1.proj_id=dbo.tmptb2.proj_id as dbo.tmptb3
    )
    inner join
(
        (dbo.task inner join dbo.rsrc on dbo.task.rsrc_id=dbo.rsrs.rsrc_id) as tmptb4)
        on tmptb3.proj_id=tmptb4.proj_id
    )

Upvotes: 1

Views: 165

Answers (2)

Taryn
Taryn

Reputation: 247850

Your code looks to have too many parentheses but it is hard to tell exactly what you are doing. So I would alter it to use something similar to this:

select *
from 
(
    select *
    from
    (   
        select *
        FROM dbo.task 
        inner join dbo.projwbs 
            on dbo.task.proj_id=dbo.projwbs.wbs_id
    ) as tmptb1
    inner join
    (
        select *
        from dbo.task 
        inner join dbo.project 
            on dbo.task.proj_id=dbo.project.proj_id
    ) as tmptb2
        on tmptb1.proj_id=tmptb2.proj_id
) as tmptb3
inner join
(
    select *
    from dbo.task 
    inner join dbo.rsrc 
        on dbo.task.rsrc_id=dbo.rsrs.rsrc_id
) as tmptb4
    on tmptb3.proj_id=tmptb4.proj_id

Replace the SELECT * with the columns that you want from each table.

I am not sure why you are doing all of those subqueries to get the data, you should be able to use:

select *
from dbo.task t
inner join dbo.projwbs w
  on t.proj_id=w.wbs_id
inner join dbo.project p
  on t.proj_id=p.proj_id
inner join dbo.rsrc r
  on t.rsrc_id = r.rsrc_id

Upvotes: 3

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

I might be missing something... but since these are all inner joins, I'm not sure what the blocks are giving you that a simplified version wouldn't also do:

from 
    dbo.task t
    inner join dbo.projwbs pw on t.proj_id=pw.wbs_id 
    inner join dbo.project p on t.proj_id=p.proj_id
    inner join dbo.rsrc r on t.rsrc_id=r.rsrc_id

Upvotes: 3

Related Questions