merging two tables with a Union query

[kfortowsky]

I have two tables, with slightly differing data structures. Each table has a few fields that the other does not (but many common fields). I want to merge them i.e. create a single table with one record for each row of the source tables (ex if source 1 has 300 rows and source 2 has 400 rows, the result has 700 rows).

My solution in other platforms is to use SQL to:

1. create a view for each table that adds the missing fields from the other table (usually with null values)

2. create a third view that merges the two tables with a "Union All" statement.

There appears to be no such statement in Caspio. But perhaps (hopefully) I am incorrect. Please advise.

/Keith Fortowsky

[MayMusic]

Are you trying to merge two tables and just get rid of those table? If yes then you can export your tables out as excel files, merge them and then import it back. Then use this table :!: :!: :!:

[kfortowsky]

Thank-you for your suggestion. The spreadsheet option certainly works - it is in fact what I am using right now. But it is clumsy and requires several manual steps. I want to automate the process as much as possible and make it accessible via the web. Thus my attempt to do it in Caspio. It would be quite simple in mySQL and that is probably my next option, but that then leaves me to make my own html forms and tables, link them to the database, etc. rather than the simplicity of Caspio.

[MayMusic]

Can you bring an example of your table design and also what you want to get out of the merge?

[kfortowsky]

A union query is exactly what I *want* to do, my question is *how* to do it in Caspio. I see no "SQL view" for queries (ex in Access), just the visual query builder.

You seem to have forgotten to include your example, other than a URL that has no real connection to the discussion, as far as I can see.

[NiceDuck]

We still dont have Union Join on views but we can use an extra table to contain a value from the tables you wanted to join as Union.

First, the extra table should have an extra text255 field to handle the Id of its parent record besides its own.

Then use task to copy the already existing records on both tables to the extra tables.

Then Triggered action to copy the new records and update updated records on both tables.

[Shiro]

I agree, @NiceDuck, However, you must set the extra table as read only. DO NOT ALLOW any applications to change/update records from extra table as it will cause chaos on the data integrity.