Combining Data to Get Consumer Sales

Hello heroes.

Looking to create an data object from a couple of MySQL tables. These tables are on different databases so joining isnt straight forward, kind of want to avoid the federated engine thing in MySQL.
So resource1 is full of purchases. UserID, ItemID, and i did a count of ItemID
resource2 has ItemID and price.
things are separate because these are expected prices (sony, microsoft, nintendo all do different things and so separation was intentional i am told)

What i want is an object that has a userID and total money spent that can be displayed in either table or graph form.

@dlbhilbig That seems like a lot but it can all be done...
I would consider getting this data from each db and constructing a new view / table with it in another table in a db of your choosing....
As per the graph or table, that's the easy part... :slight_smile: famous last words

Curious to know more about your use case too! How would the data object fit into the flow of your app?

Basically we are interested in viewing who purchases the most at a glance given a user input timeframe.

Got it! I think that, like @ScottR mentioned, having a separate table in a DB that links the information together will be the most performant. However, if you rather combine the data in the frontend, you might try using a Query JSON with SQL query to do so! With that, you can pull in the data for that range and then use the Query JSON with SQL query to run a join on it and pass data from that query to your table.

Can you let us know if that works?

Oh neat. I'll give this a go. I'll report back soon.

Ok yes this seems to be the method that will work. Doing an outer join since there is no guarantee that either source will return a row. Pretty slick thanks!