Hi,
I have have two RecordSet with one field each and the same number of data. I need a new RecordSet which is the merge of the two, so that it is now composed by two fields. Is there a way to achieve this?
Thanks.
are both recordset’s populated by the same table? and if so, does this table contain duplicate data?
I would suggest you post both SQL select statements here so we can help.
The point is that (in general) the two RecordSet do not come from the same table. For example I have in general something like this:
SELECT X FROM DATA1 --> RecordSet 1
SELECT Y FROM DATA2 --> RecordSet 2
Both RecordSet have 1 field and have the same number of data. Any ideas?
Thanks!
without knowing a tad more its hard to be 100% sure
a union query seems plausible
union with distinct
it could be a join thats required but without more detail its hard to know
I try to give you some details.
I have some SQLiteDatabase objects. They have some columns with all the same number of events. From these SQLiteDatabase I get some RecordSet, with just one variable each (I use SQLSelect with a query like SELECT VAR FROM TABLE). Now, I’d like to get two of those RecordSet (regardless the SQLiteDatabase they come from) and make a new one which should have now two variables. I hope it is more clear.
Thanks for suggestions!
based on your sql example, something like this could work with one record set:
select distinct col_1
from (select col_1 from T_TABLE_1
union all
select col_1 from T_TABLE_2) a
order by 1 asc
If you’re using multiple database files you should look at the attachdatabase method. This will allow you to attach multiple database files to a single SQLiteDatabase instance & create a single select statement across those files.
I believe you can remove the DISTINCT
the UNION by virtue of what it does automatically provides unique records (or at the ORACLE install where I work does)
Adding DISTINCT just forces an internal key and deduping process that may be extra un-necessary overhead
that is a good point Dave.
I think because I used UNION ALL, that allowed duplicate values which was counter productive of me 
so… you should be able to drop the distinct and remove the all and it should match up with what you are describing 
Union combines the results vertically, not horizontally. He wants a column from one table and a column from another table in the same row of the result set. That’s a join.
Is there any common data in the two tables you can use to join them? Otherwise, you’re going to have to combine the results in your own code, but you can’t use a RecordSet to hold the combined result. You’ll have to create a class to hold them and make an array of class objects.
Since the tables have one field each, a join is only any good if the values in each table can be the same.
There are the same number of rows. So unless the two tables are exactly the same, there will be values in one table that are not in the other.
If table one contains
X
Y
Z
and table 2 has
A
B
C
Then the only thing you can use to end up with
X,A
Y,B
Z,C
…would be the row number.
If you can get a row number from each table, then your join would be something like
select table1.data, table2.data from table1,table2
where table1.rownumber = table2.rownumber
(if you cannot find anything in the two tables in common, any attempt to join them will cause a cartesian join, giving
X,A
X,B
X,C
y,A
Y,B
Y,C
…etc … all combinations of the two values. perhaps that IS what you want?)
If you can’t get a row number from the table, read the two tables into arrays, then create a new table with two columns from the matched arrays.