David, Sorry. Should have replied earlier. Got the syntax. Funny thing was I had been keeping all of my failed syntax attempts. Went back to one that I said 'This should work' and it did. Don't know what I did different. And I've even had plenty of coffee today.
Jan ----- Original Message ----- From: "David M. Blocker" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Monday, April 18, 2005 12:40 PM Subject: [RBG7-L] - RE: Report question > Jan > > Suppose MASTER is the 1 side table, and DETAIL is the many side table, and > MasterID is the linking column > > Method 1 > > CREATE VIEW MasterDetail AS + > SELECT T1.MasterID,T1.Col1,T1.Col2,T2.Col3,T2.Col4 + > FROM Master T1 LEFT OUTER JOIN Detail T2 + > ON T1.MasterID = T2.MasterID > > Method 2 > > CREATE VEW MasterDetail AS + > SELECT T1.MasterID,T1.Col1,T1.Col2,T2.Col3,T2.Col4 + > FROM Master T1, Detail T2 + > WHERE T1.MasterID = T2.MasterID + > UNION + > SELECT T3.MasterID,T3.Col1,T3.Col2,NULL,NULL + > FROM Master T3 + > WHERE T3.MasterID NOT IN (SELECT T4.MasterID FROM DETAIL T4) > > David Blocker > [EMAIL PROTECTED] > 781-784-1919 > Fax: 781-784-1860 > Cell: 339-206-0261 > ----- Original Message ----- > From: "Jan Johansen" <[EMAIL PROTECTED]> > To: "RBG7-L Mailing List" <[email protected]> > Sent: Monday, April 18, 2005 12:38 PM > Subject: [RBG7-L] - RE: Report question > > > > Thanks Sami, David and Charlie; > > > > It was kind of what I figured. I'm working on creating a view. > > I can do what I want in a select statement but have trouble getting the > > syntax right to create a view. > > > > What I need is a view with all the columns of table 1 and all the columns > > from table 2 whether table 2 has rows or not. > > > > Been trying several permutations in creating the view but keep getting > > syntax errors. > > > > Jan > > > > ----- Original Message ----- > > From: "David M. Blocker" <[EMAIL PROTECTED]> > > To: "RBG7-L Mailing List" <[email protected]> > > Sent: Monday, April 18, 2005 9:29 AM > > Subject: [RBG7-L] - RE: Report question > > > > > > > If you have records in the master table which may NOT have matching rows > > in > > > the sub-table AND you want to include the MASTER table row in the > report, > > > your best bet is to create an OUTER JOIN view and base the report on > that > > > view. > > > > > > > > > David Blocker > > > [EMAIL PROTECTED] > > > 781-784-1919 > > > Fax: 781-784-1860 > > > Cell: 339-206-0261 > > > ----- Original Message ----- > > > From: "Charles Parks" <[EMAIL PROTECTED]> > > > To: "RBG7-L Mailing List" <[email protected]> > > > Sent: Monday, April 18, 2005 12:15 PM > > > Subject: [RBG7-L] - RE: Report question > > > > > > > > > A view/temp view or a sub-report would be a better option. Regions are > > > for moving components when the object above needs to expand and press > > > the role below it down. > > > > > > -----Original Message----- > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Jan > > > Johansen > > > Posted At: Monday, April 18, 2005 9:37 AM > > > Posted To: RB7-L > > > Conversation: [RBG7-L] - Report question > > > Subject: [RBG7-L] - Report question > > > > > > > > > Morning group, > > > > > > Need a brain check here. > > > > > > I need a one to many report! > > > > > > I need to drive a report from the ONE because there are times that the > > > MANY will not exist. > > > Is this a time for a region? > > > > > > Or should I create a view with a LEFT OUTER JOIN and just do it that > > > way? > > > > > > TIA, > > > > > > Jan Johansen > > > > > > --- RBG7-L > > > ================================================ > > > TO POST A MESSAGE TO ALL MEMBERS: > > > Send a plain text email to [email protected] > > > > > > (Don't use any of these words as your Subject: > > > INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, > > > REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) > > > ================================================ > > > TO SEE MESSAGE POSTING GUIDELINES: > > > Send a plain text email to [email protected] In the message SUBJECT, put > > > just one word: INTRO ================================================ > > > TO UNSUBSCRIBE: > > > Send a plain text email to [email protected] In the message SUBJECT, put > > > just one word: UNSUBSCRIBE > > > ================================================ > > > TO SEARCH ARCHIVES: > > > Send a plain text email to [email protected] In the message SUBJECT, put > > > just one word: SEARCH-n (where n is the number of days). In the message > > > body, place any text to search for. > > > ================================================ > > > > > > > > > > > >
