Re: Join more than 2 tables

Martin,

if I understand the query you wrote in the right way then the following should do it:

select np.project_number
, m.designer m_designer
from new_project np
, project_team m
where np.project_number(+)=m.project_number and m.discipline='M'
union
select np.project_number
, e.designer e_designer
from new_project np
, project_team e
where np.project_number(+)=e.project_number and e.discipline='E'
union
select np.project_number
, p.designer p_designer
from new_project np
, project_team p
where np.project_number(+)=p.project_number and m.discipline='P'
order by 1,2;

h.t.h.
BertJan Meinders
Oracle DBA
ASR-ICT In article <8uahpi$mni$1_at_nnrp1.deja.com>,   schmidmartin_at_my-deja.com wrote:
> I am trying to create the following view in which
> 4 subqueries are joined. How do I ensure that
> all project_number values from np are displayed
> through my join condition? I am currently
> getting 'ORA-01417: a table may be outer joined
> to at most one other table'. Leaving off the 2nd
> and 3rd (+) causes me to lose rows.
>
> Thanks
> MS
>
> select np.project_number, m.designer m_designer,
> e.designer e_designer, p.designer p_designer from
> new_project np,
> (select designer, project_number from
> project_team where discipline='M') m,
> (select designer, project_number from
> project_team where discipline='P') p,
> (select designer, project_number from
> project_team where discipline='E') e
> where
> np.project_number(+)=m.project_number and
> np.project_number(+)=e.project_number and
> np.project_number(+)=p.project_number;
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 08 2000 - 03:04:49 CST