Sytze, Here's an example of using UNION to collect data from different tables
Bill * Select data using UNION to build a 'cum file SELECT "TL " as Src,actiondate as Act_Date,LEFT(title,100) as Event,project,component,title,fileno,'TL' as source FROM tracklog ; WHERE (status = 'INIT' OR status = 'OPEN') ; and BETWEEN(tracklog.actiondate,this.dStartDate,this.dEnddate) ; UNION ; SELECT "CAL " as Src,actiondate as Act_Date,Event,project,component,title,fileno,'CAL' as source FROM thecal ; WHERE (status = 'INIT' OR status = 'OPEN') ; and BETWEEN(thecal.actiondate,this.dStartDate,this.dEnddate) ; UNION ; SELECT "PROB " as Src,actiondate as Act_Date,title as Event,project,component,title,fileno,'PRB' as source FROM theprobs ; WHERE (status = 'INIT' OR status = 'OPEN') ; and BETWEEN(theprobs.actiondate,this.dStartDate,this.dEnddate) ; UNION ; select "CHG " as Src,actiondate as Act_Date,title as Event,project,component,title,fileno,'CHG' as source FROM thechgs ; WHERE (status = 'INIT' OR status = 'OPEN') ; and BETWEEN(thechgs.actiondate,this.dStartDate,this.dEnddate) ; UNION ; select "REQ " as Src,actiondate as Act_Date,title as Event,project,component,title,fileno,'REQ' as source FROM thereqs ; WHERE (status = 'INIT' OR status = 'OPEN') ; and BETWEEN(thereqs.actiondate,this.dStartDate,this.dEnddate) ; UNION ; select "DO " as Src,actiondate as Act_Date,title as Event,project,component,title,fileno,'TSK' as source FROM thetasks ; WHERE (status = 'INIT' OR status = 'OPEN') ; and BETWEEN(thetasks.actiondate,this.dStartDate,this.dEnddate) ; UNION ; select "IDEA " as Src,actiondate as Act_Date,title as Event,project,component,title,fileno,'IDE' as source FROM theideas ; WHERE (status = 'INIT' OR status = 'OPEN') ; and BETWEEN(theideas.actiondate,this.dStartDate,this.dEnddate) ; UNION ; select "CTL " as Src,actiondate as Act_Date,title as Event,project,component,title,fileno,'CTL' as source FROM thectl ; WHERE (status = 'INIT' OR status = 'OPEN') ; and BETWEEN(thectl.actiondate,this.dStartDate,this.dEnddate) ; into cursor calsum readwrite SELECT calsum > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Sytze de Boer > Sent: Thursday, May 15, 2008 4:25 PM > To: [EMAIL PROTECTED] > Subject: Re: select from multiple tables > > > I THINK you've addressed my question. So how would you put > this into one line ? (the union all clause) > > > On Fri, May 16, 2008 at 8:15 AM, MB Software Solutions > General Account < [EMAIL PROTECTED]> wrote: > > > Sytze de Boer wrote: > > > I'm sure this is a simple one for most of you > > > > > > I have 2 (or more) free standing tables > > > table1=current data > > > table2=history data > > > The structure of both tables is identical > > > > > > I now want to do something like > > > select date,qty,product,client from table1 where > month(date)=5 order > > > by client into table temp > > > > > > But I want this from BOTH table1 and table2 > > > > > > Can this be done in ONE select statement ? > > > > > > > > > Perhaps I'm missing something, but why not use UNION ALL the 2 > > SQL-SELECTs? > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
