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. > > ================================================ > > > > > >
