Re: joining tables

Hi Ted,

In essence, you want to merge the three tables, removing records that are duplicated by another table, right?

Here are two approaches:

1) SELECT DISTINCT (field list) FROM (SELECT * from table1 UNION SELECT * from table2 UNION SELECT * from table 3).
This will remove all duplicates, regardless of their source (e.g. if table1 as two “Ted” fields, one of the rows will
beeliminated. Overlapping fields 
will be combined (e.g. only one field in the output), and non-overlapping fields will be merged. For example if t1 has
f1,f2, and f3, and t2 has 
f1, f3, and f4, and table 3 has f1, f2, and f5, the resulting output will have f1, f2, f3, f4, and f5; fields that did
notexist in the source table will be NULL 
in the output.

2) SELECT CASE WHEN t1.field1 IS NOT NULL THEN t1.field1 WHEN t2.field1 IS NOT NULL THEN t2.field1 ELSE t3.field1 END,
… 
    FROM t1 LEFT OUTER JOIN t2 ON (key field) LEFT OUTER JOIN t3 ON (key field)
Using LEFT OUTER JOIN ensures that all records from all three tables will be included and that duplicates among the
threetables will result 
in only one record. Duplicates within a table create a problem. For example, if t1 has “Ted” twice and t2 has “Ted”
twice,there will be four “Ted” 
records in the output.

If you can live with the elimination of duplicate rows within a table, option 1 is the easiest as you don’t have to
specificallycode each field to be selected. 

> On Sep 3, 2019, at 4:01 AM, TedJones <ted@mentra.co.uk> 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
>
>