I'll answer my own question - as I've now tried both ways: Joining the 2 views is significantly faster than the union.
Regards, Alastair. ----- Original Message ----- From: "Alastair Burr" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Tuesday, May 31, 2005 4:04 PM Subject: [RBG7-L] - Re: Help needed with creating a view with a join: > Thanks David, Jan & Larry. > > I half had a feeling there might be a two table limit for the join. It's a > shame but there are ways around. > > Naturally, I'd prefer one command instead of two if possible but I also like > to try and avoid views based on views in case there's a problem with the > sequence of the views in a restore situation. > > I'm guessing that joining two views would give the result quicker that the > union command that David suggested - there's many more rows in the original > 4 table view than there are non-null rows in the view with the join. Any > thoughts on the better solution for speed? I can't think of any other reason > to choose between the two other than the single command? > > Regards, > Alastair. > > > > ----- Original Message ----- > From: "jan johansen" <[EMAIL PROTECTED]> > To: "RBG7-L Mailing List" <[email protected]> > Sent: Tuesday, May 31, 2005 1:57 PM > Subject: [RBG7-L] - Re: Help needed with creating a view with a join: > > > > Alastair, > > > > Another thing you can do is build up what you need by basing a view upon > > another view. > > I recently had to do this as I needed to take 5 tables in an older app to > 1 > > and they all needed > > to be outer joined. > > > > View1 = LeftJoinTable1 and Table2 > > View2 = LeftJoinView1 and Table3 > > View3 = LeftJoinView2 and Table4 > > ResultView = LeftJoinView3 and Table5 > > > > Cumbersome but sometimes it takes more than one command to do what you > need > > to do. > > > > Jan > > > > -----Original Message----- > > From: "David M. Blocker" <[EMAIL PROTECTED]> > > To: [email protected] (RBG7-L Mailing List) > > Date: Tue, 31 May 2005 07:49:37 -0400 > > Subject: [RBG7-L] - Re: Help needed with creating a view with a join: > > > > > Alastair > > > > > > You can't do what you're trying to do - an OUTER JOIN in R:Base is > > > limited > > > to TWO tables OR VIEWS. > > > > > > So you CAN outer join your Artist_Tracks_View with your COMMENTS table. > > > > > > OR, you can use SELECT UNION like this: > > > > > > SELECT > > > T1.Artist,T1.All_Titles,T1.Playing_Time,T1.DiscIDNum,T1.TrackNumber,+ > > > T2.Full_Name,T3.Artist,T3.All_Titles,T4.Full_Name,T5.Long_Comments + > > > FROM Tracks T1,All_Names T2,Tracks T3,All_Names T4,All_Comments T5 + > > > WHERE T1.TrackNumber > 0 AND T1.Artist = T2.IDNum + > > > AND T3.DiscIDNum = T1.DiscIDNum + > > > AND T3.TrackNumber = 0 + > > > AND T4.IDNum = T3.Artist AND T1.DiscIDNum = T5.DiscIDNum > > > UNION > > > SELECT > > > T1.Artist,T1.All_Titles,T1.Playing_Time,T1.DiscIDNum,T1.TrackNumber,+ > > > T2.Full_Name,T3.Artist,T3.All_Titles,T4.Full_Name,NULL + > > > FROM Tracks T1,All_Names T2,Tracks T3,All_Names T4 + > > > WHERE T1.TrackNumber > 0 AND T1.Artist = T2.IDNum + > > > AND T3.DiscIDNum = T1.DiscIDNum + > > > AND T3.TrackNumber = 0 + > > > AND T4.IDNum = T3.Artist AND T1.DiscIDNum NOT IN (SELECT DiscIDNum FROM > > > All_Comments) > > > > > > > > > David Blocker > > > [EMAIL PROTECTED] > > > 781-784-1919 > > > Fax: 781-784-1860 > > > Cell: 339-206-0261 > > > ----- Original Message ----- > > > From: "Alastair Burr" <[EMAIL PROTECTED]> > > > To: "RBG7-L Mailing List" <[email protected]> > > > Sent: Tuesday, May 31, 2005 4:40 AM > > > Subject: [RBG7-L] - Help needed with creating a view with a join: > > > > > > > > > > Please can anyone put me right on this problem: > > > > > > > > I am trying to create a view based on 4 tables and then add a fifth > > > table > > > > where there may not be rows to match every row in the other 4 tables. > > > > > > > > Currently, I have a view for the 4 tables where there is a linking > > > row for > > > > each table and another view which gives me one of those tables with > > > the > > > > fifth table that uses an outer join. Separately, both work fine. > > > > > > > > Looking at the help for views and joins I am not sure how the syntax > > > should > > > > run in sequence to do what I want - or, even, if it can be done. > > > > > > > > These are the two views that work: > > > > > > > > CREATE VIEW Artist_Tracks_View + > > > > (Artist,AllTitles,PlayingTime,DiscIDNum,TrackNumber,+ > > > > FullName,DiscArtist,DiscTitle,DiscArtistName) + > > > > AS SELECT > > > > T1.Artist,T1.All_Titles,T1.Playing_Time,T1.DiscIDNum,T1.TrackNumber,+ > > > > T2.Full_Name,T3.Artist,T3.All_Titles,T4.Full_Name + > > > > FROM Tracks T1,All_Names T2,Tracks T3,All_Names T4 + > > > > WHERE T1.TrackNumber > 0 AND T1.Artist = T2.IDNum + > > > > AND T3.DiscIDNum = T1.DiscIDNum + > > > > AND T3.TrackNumber = 0 + > > > > AND T4.IDNum = T3.Artist > > > > > > > > > > > > CREATE VIEW Trck_Comment_View + > > > > (DiscIDNum, TrackNumber, AllTitles, LongComment, Artist) + > > > > AS SELECT + > > > > T1.DiscIDNum,T1.TrackNumber,T1.All_Titles,T2.Long_Comments,T1.Artist > > > + > > > > FROM Tracks T1 + > > > > LEFT OUTER JOIN All_Comments T2 ON T1.DiscIdNum = T2.DiscIdNum + > > > > AND T2.DiscIDNum = T1.DiscIDNum AND T2.TrackNumber = T1.TrackNumber > > > > > > > > > > > > What I want to do is add the "Long_Comments" column from the second > > > view > > > to > > > > a new version of the first one. > > > > The syntax that I am trying gives me an error as soon as I add in the > > > third > > > > table and with or without any additional where/and clauses: > > > > > > > > CREATE VIEW `Artist_Tracks_View` + > > > > (Artist, AllTitles, PlayingTime, DiscIDNum, > > > TrackNumber, > > > > TrackComment, FullName) + > > > > AS SELECT + > > > > T1.Artist, T1.All_Titles, T1.Playing_Time, T1.DiscIDNum, > > > T1.TrackNumber, > > > > T2.Long_Comments, T3.Full_Name + > > > > FROM Tracks T1, All_Names T3 + > > > > LEFT OUTER JOIN All_Comments T2 ON T1.DiscIdNum = T2.DiscIdNum + > > > > AND T2.DiscIDNum = T1.DiscIDNum AND T2.TrackNumber = T1.TrackNumber > > > > > > > > Is it possible/permissible to do this and, if so, what is the correct > > > > sequence for the syntax: > > > > > > > > Create... > > > > As select... > > > > From... > > > > Join... > > > > Where... > > > > And... > > > > > > > > or, maybe: > > > > > > > > Create... > > > > As select... > > > > From... > > > > Where... > > > > And... > > > > Join... > > > > > > > > > > > > Thanks in advance for any assistance, > > > > Regards, > > > > Alastair. > > > > > > > > ---------------------------------- > > > > A D B Burr, > > > > St. Albans, UK. > > > > ---------------------------------- > > > > [EMAIL PROTECTED] > > > > ---------------------------------- >
