DaDaBIK
This forum is devoted to the discussion about the software DaDaBIK database front-end
the PHP Database application generator for MySQL, PostgreSQL and SQLite; also available for Wordpress.

Welcome! Log In Create A New Profile

Advanced

Overlaying Data

Posted by kiksekage 
Overlaying Data
November 20, 2017 11:17PM
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:


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
     +------------------------

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.
Re: Overlaying Data
November 21, 2017 03:18AM
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,

________________________________________________________
Eugenio |\.-./| DaDaBIK developer and project admin
Re: Overlaying Data
December 02, 2017 11:04PM
Would use something like this for the view:

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

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.
Re: Overlaying Data
March 28, 2018 01:23PM
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.
Sorry, only registered users may post in this forum.

Click here to login