Select SQL - how to combine 2 tables, not just "join" them

[dbMark]

I need to get selected records out of two identically structured tables, lets call them arc2001 and arc2002. Rather than run 2 SELECT on each of them, I need to do it at the same time. But every combination I've tried ends in errors or multiplied records. Most examples and questions here, as well as the explanations for the JOIN clauses apply more for parent-child relationships but I simply need all matching records from the 2 tables. In other words, can SELECT concatenate the tables and then extract out the records I want? (Back in the FP DOS days I did this, but that must have been a lifetime ago...) So if table arc2001 has 5 records for activity="RESTART" and the other table arc2002 has 7, I need to end up with 12 records holding the first 3 fields, something like this... (but this example failed and must need some more help, of course!)

SELECT field1, field2, field3 WHERE activity="RESTART" FROM arc2001 INNER JOIN arc2002 INTO TABLE arctotal

 

[craigsboyd]

Select * from arc2001 Union Select * from arc2002 into table arctotal

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[dbMark]

I just knew I had a mental block! Thanks, as I look again at the VFP manual, I found it at the bottom lurking far away from the JOIN arguments, so I missed it. I'll probably use UNION ALL to be sure any seeming duplicates are not eliminated.

 

[craigsboyd]

dbMark,

Glad I could help.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[sajudubai]

You can eliminate duplicate record useing sele distint ... option

 

[dbMark]

Ok, I'm using UNION now to combine selected records from up to 3 tables and it works just fine during my testing at the console but fails on the line executing the SQLcommand (inside sqlFilter) when I try to call it as a COM object from my ASP page.

It failed when sending the results to a cursor, so I changed ithe destination to a table. I was sure that would work, writing the output to a table, but I still lock up. Then when I try a second time (before stopping/restarting the service) I get an error.

The error I get is: "Error in line 345 User-interface operation not allowed at this time. 1031" There is no error 1031 in VFP so far as I know. Where did I go wrong?

[ode]= "x:\mydata\"+this.past_year+"\dt"+this.client+".dbf"
file2 = "x:\mydata\"+this.curr_year+"\dt"+this.client+".dbf"
file3 = "x:\mydata\dt"+this.client+".dbf"
file4 = "ignoreme" && will have SQL temp table name
sqlFilter=""
if file(file1)
this.beg_range=gomonth(this.beg_range,-12) && include prior year in maximum range
sqlFilter="select * from "+file1+" as hp where " ;
+"hp.ee_no=["+this.ee+"] and " ;
+"between(hp.sav_date,this.beg_date,this.end_date) "
endif
if file(file2)
sqlFilter=sqlFilter+iif(len(sqlFilter)=0,"","union all &quot ;
+"select * from "+file2+" as hc where " ;
+"hc.ee_no=["+this.ee+"] and " ;
+"between(hc.sav_date,this.beg_date,this.end_date) "
endif
if file(file3)
sqlFilter=sqlFilter+iif(len(sqlFilter)=0,"","union all &quot ;
+"select * from "+file3+" as ic where " ;
+"ic.ee_no=["+this.ee+"] and " ;
+"between(ic.sav_date,this.beg_date,this.end_date) "
endif
if len(sqlFilter)>0
file4="ee_"+this.ee+"_"+substr(str(rand(-1),9,7),6,3)
* add destination into a table
sqlFilter=sqlFilter+"order by 2 into table X:\temp\"+file4
* run SQL query
&sqlFilter && <-- I get the error here!
endif[/code]

Where did I go wrong???

 

[dbMark]

Resolved in thread184-635517.

 

[jimoo]

Verify the table structures in the live environment are the same. Unions need to be exact. You can trick-it into being exact if they are not. Example - The 2nd table does no have a type field so I added the SPACE(3) function.

SELECT lookup1.distcode, lookup1.distname, lookup1.type ;
FROM lookup1 ;
UNION ;
SELECT lookup2.distcode, lookup2.distname, SPACE(3);
FROM lookup2

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com