hanskait hanskait - 1 month ago 5
MySQL Question

How to create a Mysql View between 2 tables

I want to create a view between a table that has data and another table that has a flag that the field must show or not.

TABLE_EXAMPLE

+---------+---------+---------+-----------------+
| id | field_1 | field_2 | field_3 |
+---------+---------+---------+-----------------+
| 1 | test | 500 | another content |
+---------+---------+---------+-----------------+
| 2 | blah | 800 | text_lorem |
+---------+---------+---------+-----------------+
| 3 | hi! | 100 | lorem_impsum |
+---------+---------+---------+-----------------+


REFERENCE_TABLE (This table is joined with the other table by table_name, field_name,entry_id. The other field is show/not_show flag)

+--------------+------------+----------+------+
| table_name | field_name | entry_id | show |
+--------------+------------+----------+------+
| TABLE_EXAMPLE| field_1 | 1 | 0 |
+--------------+------------+----------+------+
| TABLE_EXAMPLE| field_2 | 1 | 1 |
+--------------+------------+----------+------+
| TABLE_EXAMPLE| field_3 | 1 | 0 |
+--------------+------------+----------+------+
| TABLE_EXAMPLE| field_1 | 2 | 1 |
+--------------+------------+----------+------+
| TABLE_EXAMPLE| field_2 | 2 | 0 |
+--------------+------------+----------+------+
| TABLE_EXAMPLE| field_3 | 2 | 1 |
+--------------+------------+----------+------+
| TABLE_EXAMPLE| field_1 | 3 | 1 |
+--------------+------------+----------+------+
| TABLE_EXAMPLE| field_2 | 3 | 1 |
+--------------+------------+----------+------+
| TABLE_EXAMPLE| field_3 | 3 | 0 |
+--------------+------------+----------+------+


Here is the resultant view. In case the flag is 0 the field content must be NULL

RESULT_TABLE

+---------+---------+---------+-----------------+
| id | field_1 | field_2 | field_3 |
+---------+---------+---------+-----------------+
| 1 | NULL | 500 | NULL |
+---------+---------+---------+-----------------+
| 2 | blah | NULL | text_lorem |
+---------+---------+---------+-----------------+
| 3 | hi! | 100 | NULL |
+---------+---------+---------+-----------------+


Any idea or suggestion? I couldn't get it.

Answer

If the REFERENCE_TABLE has flags for all fields of each entity, then you can use this:

SELECT
  T1.ID,
  MAX(CASE WHEN T2.field_name = 'field_1' AND T2.show = 1 THEN T1.field_1 END) field_1,
  MAX(CASE WHEN T2.field_name = 'field_2' AND T2.show = 1 THEN T1.field_2 END) field_2,
  MAX(CASE WHEN T2.field_name = 'field_3' AND T2.show = 1 THEN T1.field_3 END) field_3,
FROM TABLE_EXAMPLE T1
  JOIN REFERENCE_TABLE T2
    ON T1.id = T2.entity_id
WHERE T2.table_name = 'TABLE_EXAMPLE'
GROUP BY T1.ID

or use LEFT JOIN if you omit some flags. Then the omitted flags will treats as 0.

Comments