outer joins to more than one table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: outer joins to more than one table

  1. #1
    Join Date
    Jun 2003
    Posts
    47

    outer joins to more than one table

    Hi everybody,

    I had to do this :

    select thf.idhtmlfile as idPage,twa.idwebtemplatesareas as idarea,
    twa.name as name,twa.t_row as t_row,toc.content as content,
    toc.name as title,ta.tagxml as tag
    from tblarea ta,tblhtmlfile thf,tblwebtemplatesareas twa,
    tblonlinecontent toc,tbltemplateandcontent ttc
    where thf.idtemplate=twa.idwebtemplate
    and ta.idarea=twa.idarea
    and toc.idonlinecontent=ttc.idonlinecontent
    and thf.idhtmlfile=ttc.idhtmlfile(+)
    and twa.idwebtemplatesareas=ttc.idwebtemplatesareas(+)
    order by idpage,twa.idarea asc;

    but U can not outer join a table to more than one table so I was wondering how can this be done.

    Is this right :

    select tg1.idhtmlfile as idPage,tg1.idwebtemplatesareas as idarea,
    tg1.name as name,tg1.t_row as t_row,tg2.content as content,
    tg2.name as title,tg1.tagxml as tag
    from tblonlinecontent toc,tbltemplateandcontent ttc,
    (select ta.tagxml,ta.idarea,thf.idhtmlfile ,
    idwebtemplatesareas,twa.name,twa.t_row
    from tblarea ta,tblhtmlfile thf,tblwebtemplatesareas twa
    where ta.idarea=twa.idarea and thf.idtemplate=twa.idwebtemplate) tg1,
    (select ttc.idhtmlfile,ttc.idwebtemplatesareas,toc.content,toc.name
    from tblonlinecontent toc,tbltemplateandcontent ttc
    where toc.idonlinecontent=ttc.idonlinecontent) tg2
    where tg1.idhtmlfile=tg2.idhtmlfile(+)
    and tg1.idwebtemplatesareas=tg2.idwebtemplatesareas(+)
    order by idpage,tg1.idarea asc

    or this one is right:

    select thf.idhtmlfile as idPage,twa.idwebtemplatesareas as idarea,
    twa.name as name,twa.t_row as t_row,toc.content as content,
    toc.name as title,ta.tagxml as tag
    from tblarea ta,tblhtmlfile thf,tblwebtemplatesareas twa,
    tblonlinecontent toc,tbltemplateandcontent ttc,tbltemplateandcontent ttc2
    where thf.idtemplate=twa.idwebtemplate
    and ta.idarea=twa.idarea
    and toc.idonlinecontent=ttc.idonlinecontent
    and thf.idhtmlfile=ttc.idhtmlfile(+)
    and twa.idwebtemplatesareas=ttc2.idwebtemplatesareas(+)
    order by idpage,twa.idarea asc;

    They give different results and I don't actually know why?
    Can anyone have a look at it please.
    Thank you

  2. #2
    Join Date
    Jun 2003
    Posts
    47
    well, I tested it the first version is the right one.
    Thanks to every body.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width