Re: Join more than 2 tables

Your version only returns two columns (project_number, designer) due to the fact that you're using a union.

I want a JOIN to end up with 4 columns (project_number, m_designer, p_designer, e_designer).

In article <8ub4ve$5ge$1_at_nnrp1.deja.com>,   sigdock_at_my-deja.com wrote:
> 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.
>

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