INNER JOIN tables without renaming fields

Question

Source Table Structures

I have two tables that I would like to combine. Two columns act as keys, and rest of fields are values or attributes.

TABLE A (Has both KeyA and KeyB) :
KEYA | KEYB | Val1 | Val2 | Val3 | ... | ValN
---------------------------------------------
keyA1| keyB1|  V11 |  V12 |  V13 | ... |  V1n 
keyA1| keyB2|  V21 |  V22 |  V23 | ... |  V2n
keyA1| keyB3|  V11 |  V12 |  V13 | ... |  V1n
              ....
keyAm| keyB1|  Vm1 |  Vm2 |  Vm3 | ... |  Vmn


Table B (only keyA present):
KEYA |  Str1  |   Str2 
----------------------
keyA1|  str11 |  str12 
keyA2|  str21 |  str22
        ....
keyAm|  strm1 |  strm2

Result Table Structure Needed

I would like to combine the two tables like so Str1 and Str2 from TABLEB are to be mapped to TABLEA using KeyA.

TABLE C (contains both KeyA and KeyB) :
KEYA | KEYB | Val1 | Val2 | Val3 | ... | ValN |     Str1 | Str2
------------------------------------------------------------------
keyA1| keyB1|  V11 |  V12 |  V13 | ... |  V1n |    str 11| str 12
keyA1| keyB2|  V21 |  V22 |  V23 | ... |  V2n |    str 11| str 12
keyA2| keyB3|  V11 |  V12 |  V13 | ... |  V1n |    str 21| str 22
              ....
keyAm| keyB1|  Vm1 |  Vm2 |  Vm3 | ... |  Vmn |    str m1| str m2

Issue

Sometimes, new value fields (Val n) get added to Table A, but schema for Table B remains constant. If I combine the tables like so -

create TABLEC as
select a.*, b.Str1 as Str1, b.Str2 as Str2 from
TABLEA a INNER JOIN TABLEB b ON a.keyA=b.keyA

The resultant table has field names as a.val1, a.val2 etc.

This is what I currently use

create TABLEC as
select a.Val1 as Val1,
a.Val2 as Val2,
  ...
a.Valn as Valn,    

b.Str1 as Str1,
b.Str2 as Str2
from TABLEA a INNER JOIN TABLEB b ON a.keyA=b.keyA

I have to modify code everytime an additional Val field is added. Is there a way to create TABLEC directly so that the field names from TABLEA and TABLEB are preserved (keyA would be the common field).

I think you meant `KEYB` instead of `KEYA` as the second column name in the result table. — Michel de Ruiter, Oct 18 '17 at 10:01
Which DBMS do you use? Microsoft SQL Server? MySQL? Oracle? There is no standard way that works with all databases. — Michel de Ruiter, Oct 18 '17 at 10:02
Are you creating a table or just selecting a dataset? I see no DDL here — JohnHC, Oct 18 '17 at 10:03
" If I combine the tables like so - " The resultant table has field names as a.val1, a.val2 etc. That's strange — Kaushik Nayak, Oct 18 '17 at 10:08
@wildplasser - I am not modifying TABLE A. I am trying to create a new TABLE C — tdk, Oct 18 '17 at 10:15
@JohnHC - missed it 1st time, added the statements now. Thanks ! — tdk, Oct 18 '17 at 10:19
`... everytime an additional Val field is added...` Added to tablea? — wildplasser, Oct 18 '17 at 10:25
IIRC you would like to use `*` except for one column. Check https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea — Michel de Ruiter, Oct 18 '17 at 10:31

score 1 · Answer 1 · answered Oct 18 '17 at 10:58

As @wildplasser alludes to in the comments, the fundamental issue is that you are "regularly" adding columns to table A. In a relational database, adding columns to an existing table should not be a "regular" operation. Okay, sometimes it is necessary, but that should be because of changes to the data model.

Having columns names "val1", "val2", and so on is suspicious. There are numerous methods for storing such data in a more SQLish way, some of which depend on the functionality of the underlying database:

Using a junction table.
Using an entity-attribute-value data model (or more likely, a hybrid).
Using JSON or XML columns to store lists.
Using arrays.
Using nested tables.

The proximal cause of your problem is based on the definition of the view. When a view is defined, the * is expanded to all columns. This expansion doesn't occur when the view is used. You can readily see this by looking at the columns of the view which is stored in the metadata.

Changes to the underlying table do not necessarily result in the view being reparsed (although changes to underlying tables could invalidate the view).

Some databases allow triggers on DDL (data definition language). If your database supports such triggers, you could actually have the database modify the view when a new column is added. Alternatively, you could wrap the DDL changes in a stored procedure and include the changes to the view when new columns are added.

INNER JOIN tables without renaming fields

Source Table Structures

Result Table Structure Needed

Issue

1 Answers1