Overlaying Data

kiksekage

New member
I have a database where there are two tables: T1, T2
My goal is to "overlay" rows from T2 on top of the T1 row with a matching index, so that any non-null values in T2 rows will supersede those from the T1 row.

Example:

[pre]

Table T2
+------------------------
| id t1_id color ready
+------------------------
| 80 1 red <null>
| 81 2 <null> yes
+------------------------

Table T1
+-----------------------
| id color ready
+-----------------------
| 1 blue no
| 2 blue yes
+-----------------------

Overlay:
+------------------------
| id t1_id color ready
+------------------------
| 80 1 red no
| 81 2 blue yes
+------------------------

[/pre]

Any suggestions for how to display the output ("Overlay") in DaDaBIK?

Is there another paradigm in MySQL ( using InnoDB ) that might better serve what I am trying to do?

Notes:
Each "t1_id" may appear multiple times in T2 but only one row will be selected at a time.
Each "id" can only occur once in T1.
 

eugenio

Administrator
Staff member
Hello,
it seems to me you want to create a table that is the result of a JOIN of T1 and T2 based on t1_id; if this is the case and you are familiar with SQL, you can create a VIEW and install the view in DaDaBIK.

Best,
 

NorbertH

New member
Would use something like this for the view:

[pre]
SELECT
t2.id,
t2.t1_id,
IFNULL (t2.color,t1.color) AS color,
IFNULL (t2.ready,t1.ready) AS ready
FROM
table2 AS t2,
table1 AS t1
WHERE
t2.t1_id = t1.id
[/pre]

In phpmyadmin if you insert the query and scroll down to "Query result operations" there should be a button "Create View"

As the generation of the view is onedirectional because of the IFNULL() statement , you cannnot edit the fields in the view.
Mysql cannot decide what field/table to write to.
 

kiksekage

New member
Thanks for the suggestion, NorbertH! That is a good way to get the results for read-only. I may end up doing something custom but it's good to keep options in mind.
 
Top