Combine two RecordSets from two databases

I have two PostgreSQL databases with data that I would like to combine in one table for displaying on the screen in a web application. I’ve done some searching through Google and this forum to see if something like this has been answered in the past. All I’ve seen so far is doing JOINs, but these two tables are in two different databases so a JOIN won’t work.

There are only about 15 records total that will get pulled from the tables for each request. Do any of you have recommendations on how I can go about doing this?

Thank you in advance…

[quote=425826:@Robert Litchfield]I have two PostgreSQL databases […]
All I’ve seen so far is doing JOINs, but these two tables are in two different databases so a JOIN won’t work.
Sure, this is possible, even with optimal performance using the postgres_fdw or the legacy dblink extension. An alternative way would be to copy both data sets to a temporary in-memory SQLiteDatabase and do the join there. But to display 15 records in a listbox, why do you need them to be in the same recordset at all?

@Tobias Bussmann Combining the recordsets was my initial thought…if there is a more efficient way to combine them, so they can be displayed in a particular order (by time in this case) then I’m open to anything.

Create an in-memory sqlite DB, create a table 1 and table2. Insert data into table1 from postgresql-1, Insert data into table2 from postgresql-2, do joins or whatever needed; close the in-memory database.

am amazed that Attach is only available for SQLite?
because that is the Easiest way to access multiple physical databases from a single query

This might not be good for large record counts, but you could query as many databases / tables as you’d like and then add the data to a Dictionary. The tables could be very different as you can massage the data as you add it to the Dictionary. This would also work for any data source like xml, json, or any api too.

Dim rs, rs2 As RecordSet

rs = …
rs2 = …

SORT by Time_Col (syntax and cmd order to be written correctly, this is just an idea).

Or, if you already have the data in a Listbox, sort the Listbox by the Time column…

This was one direction I considered going. The pair would have been time:event. That idea failed when I realized there might be multiple events at one time so I couldn’t figure out how to make one time = two different events. :frowning:

Could you expand on that a bit with an example?

To provide context…this is for a new digital display for one of our early education galleries. The picture includes data from one of the databases, I need to add additional information from a second one (in the blue and purple time boxes).

screen shot

I love some good test data :stuck_out_tongue:

Thanks for that. Could you have a Dictionary of Timestamps that contain a Dictionary Array of Events?

Something like:

  • 2019-02-27 10am ( Baby Flying , Baby Swiming )
  • 2019-02-27 1pm ( A Thing )
  • 2019-02-27 3pm ( Toddler Tantrum )

Then you could loop thru the Dictionary of Timestamps and know how many events it as based on the Ubound of the Dictionary Array of Events to then draw what you need. Since “2019-02-27 10am” has two Events, you’d know to draw them at half width.

I think that’d work! :slight_smile: