Yet another Concatenation & Join problem

[#1]

I have a master products table with (simplifiyng):

Code

Description

GroupCode

and several products tables coming from different databases (i have a loop in the script), with identical structure:

Code

Company

StandardCost

I would likt to see all this as a single table, like this:

Products:

Code

Description

GroupCode

Company

StandardCost

In other words, I need Description and GroupCode taken from the master database and StandardCost taken from each single company table.
The problem is that, If I try by concatenating the master with the other tables, I get duplicated rows from each code, one with correct Description and GroupCode, and others from each other table with null values for Description and GroupCode.
On the other hand, I tried also with a JOIN in each table with the master, but in this case, I get n table labels "Product, Product-1, etc..." and I need to have a single resulting table. By the way, why I get different table labels, if the tables are identical?

I am sure there is a trivial solution I'm missing....

Thanks

[sparur]

Hello Paolo.

You should use a JOIN statement, for example:

Products:

LOAD Code,
Description,
GroupCode

FROM master products table;

LEFT JOIN (Products)

LOAD Code,
Company,
StandardCost

FROM other table;

after this you get table with such structure:

Products:
Code
Description
GroupCode
Company
StandardCost

[#3]

Thanks Anatoly,

actually, i didn't find the way to do that, because I have one master table, but several company tables, like that:

- MASTER DB CONNECTION

LOAD.... FROM master products;

- COMPANY 1 DB CONNECTION
.... other stuff
LOAD .... FROM other table
.... other stuff

- COMPANY 2 DB CONNECTION
.... other stuff
LOAD .... FROM other table
.... other stuff

...etc...

In this case, the script you are suggesting is working only for the first company table.

[#4 · Accepted solution]

Hi Paola,

The trick is to first load the company tables first after each other. If the tables have the same field structure, they are concatenated automatically to 1 table containing the product costs for all companies. Then at the last step, you can do a right join with the table containing the products and the groups.

The code would look like this:

Products:

LOAD
Code,
Company,
StandardCost
FROM
Company1;

LOAD
Code,
Company,
StandardCost
FROM
Company2;

RIGHT JOIN
LOAD
Code,
Description,
GroupCode
FROM
Products;

However, you could also consider to keep 2 tables (1 with products and another with the company/price info). I would prefer this myself, as it avoids double product keys in the table. In the frontend, the data would look the same to an end user. I've included a zip file with both examples attached.

I hope this answers your question.

Daniel

[#5]

Thank you Daniel,

I was triyng also with a pre-joined SQL view across the database to load from. I will check wich is the faster solution...

Thanks