Re: joining tables

Not having unique IDs from the source tables will present a problem in 
that you could have more than one record in one of the tables with the 
same data.

It looks like you will need to determine the fields that make each row 
unique but common in all three tables. Then, you can match up records 
from the three tables.

The next issue is if there is similar data in similar columns, which 
data takes prescience. That will be something you have to decide. I 
think the best way to do that is to import the most accurate table 
first. If a record from the second table matches a record from the first 
table, only import column values that weren't already populated by the 
first import. Then do the same thing with the 3rd table and so on.

I think the overall process will be to import each CSV file into a 
Postgres work table of it's own. Then, import the first table into a 
master table that contains every field. Do the same with the second 
import with the additional logic to only set a column value if it wasn't 
already set by the first (prior) import. Then, do the same with the 
third table and so on. By getting the CSV files into Postgres first, it 
will be easier to manage cases where a column between the two tables is 
the same data but the names are different. I think it will also perform 
better.

The import of the first table into the main table will be a simple 
INSERT query. For each of the rest of the tables I think I would first 
run an UPDATE query with an INNER JOIN that updates column values of 
matching records but only if the column isn't already populated. Then, 
run an INSERT query using the second table on the left side of a LEFT 
OUTER JOIN and the main table on the right side and only return records 
from the left hand table that don't match any records on the right hand 
table (i.e. fields in right table are null). Do the same for the rest of 
the tables.

A different way to perform the INSERT query would be to use a sub query 
in the WHERE clause of the second table that only returns records that 
don't have matches in the main table. This will be easier if each of the 
source tables has it's own unique ID across all source tables. If you 
don't have unique IDs, you can use a concatenation of key field values.

SELECT columns
FROM table2
WHERE [concatenation of ] key_column(s) in table2 NOT IN (SELECT 
[contenation of] key_column(s) FROM table1);

*/Patrick Headley/*
Linx Consulting, Inc.
(303) 916-5522
[email protected]
www.linxco-inc.com

On 9/3/19 5:01 AM, TedJones wrote:
> Hi Patrick
>
> Thanks for your response. The tables that I gave were just an example and
> there is no relevance in the friend/yes or names/contact details in
> different tables.
>
> To clarity, another example would be three tables - shop1, shop2 and shop3.
> Each would have products so that would be a common column but product price
> may also be a common column. However, there would be also different column
> names for each shop. (columns with same names would be of the same data
> type).
>
> I have no control over the three tables that I get as csv files. It should
> be easy enough, if needed to add a primary key column (to each?) after
> reading in the csv files into tables.
>
> So, generally, what I am trying to achieve is to combine three tables into
> one table where some of the columns are the same and some are not. e.g table
> 1 with 12 columns; table 2 with 10 columns where 4 are the same as in table
> 1; table 3 with 4 columns where 1 is the same as in table 1 and 1 the same
> as in table 2. The number of columns in the result table would then be 12
> (table 1) +  6 (new ones from table 2) + 2 (new ones from table 3) = 20
> columns.
>
> Usually the data rows from each table will be different but not always. If
> data in a column common to all three tables e.g product name, was the same
> from all tables i.e. same product name then all the information about that
> product from the three tables would be in all the columns for that data row.
> I hope that’s clear. i.e. the difference between example1 and example2 in my
> original question.
>
> In this was in reverse it would start with the large result table and use
> three SELECT statements to create the three tables.
>
> Thanks
> Ted Jones
>
>
>
>
>
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html
>
>