Filter a table using data from another table

Hello everyone,

it happens that I need to filter a database table using the information from an EM table, for example:

In EM I manipulate my data and the result is a table (T1) with a single column “CustomerCode” and few rows.
After that I need to add few information taken from the Customer Registry (description, address etc.).

Usually, at this point, we create another EM table (T2) that is the SELECT * from the customer registry, then we merge T1 with T2 and add all the data we need. But I see that in this way EM read every record in T2.

So we found another way, but I need to understand if it’s a correct one or if I miss something:

  • We’ve got T1;
  • We create a module, named “Read customer info” with the “customer code” parameter. In this module we run a Select * from customers where customer_code={parameter};
  • We iterate T1 records on the newly created module;

The performance are really good, I need to know if it’s the best way of someone can suggest a better method to achieve this.

Yes, there is a better way. The “Select matching database rows” action does exactly that - imports rows from a database table which IDs (keys) are in an EasyMorph table. The action requires a write permission in the target database as it creates a temporary table.

The action is described in the “Advanced topics” part of this tutorial article: Loading data from a database

Hello Dmitry,

I tried the “Select matching database rows” but the problem is that if in the start table I’ve got other information than the key, the action will remove them.

For example:
Customer code, Number of bought item
1234, 500
125, 700

if I select matching database rows to retrieve the customer address, the result will be:
1234, Asda Street 44 - NY
125, Jason Street 128/1 WY

Ok, it’s true that I can run the select matching before calculating the number of products (in the example), but it means that I’ve to carry alot of information throught the entire flow (or I’m wrong?)

That’s correct, the rest of the columns is removed, but you can easily merge them back (e.g. “Number of bought item” in your example) after you did “Select matching” because the key column remains.

Hello everyone,

I’ve got another question about the Select matching database: is there any way to use a custom SQL instead of a table?

My customer send me the Item+Color+Size code, and I need to keep only the rows with a valid code, so atm I read the entire Item+Color+Size registry (400K records) and then use the Keep/remove matching.

I thought that I could use the Select matching, but the source table isn’t a table but a custom SQL of three joined table, or is there a better solution?

You can try making the custom SQL a database view and then work with it as if it was a table.

It was my 1st thought, but in that case I can’t create the view.