Create view using multiple tables

[Priit Pärl]

Main idea is that I need to get values from different tables.

Basically main idea is to select all values from Table B and Table C, but selecting only values from Table C which are not present in Table B (but at the same time I need to left join Table C to get text column value). Table B and Table C has similar structure. They both have ref_num (ID) and text value. Also Table B holds Table C ref_num, because when Table C entity is modified ("modifiable_column"), then record is saved into Table B, but "default" value text column is taken from Table C.

It's something like. Let's say we have default rules (Table C - always same values forever), then we have custom rules (Table B). Table D holds version of each rule with current as end_date IS NULL. Default values have default "modifiable_column" as 'N' as mentioned before. Now, let's say I take one rule from Table C and change "modifiable_column" to 'Y'. Then new row is created into Table B (with ref_num, table_c_ref_num, text = NULL). It means that this rule is now custom for this particular TabelA ref_num, at the same time new row is inserted into Table D (holding new row ref_num as table_b_ref_num and new "modifiable_column" value). Now, when I want to select custom rules, default rules and versions (end_date IS NULL). I have to join Table B, Table C and Table D. But as Table C has always same rules, I only need to join it to get the text value. And I have to make sure I won't select duplicates. Meaning if Table C has 10 default rules, now one was modified and custom rules (Table B) has 1 rule. Then I have to so said select 1 from Table B and 9 from Table C, but at the same time I need to join Table C text value for this custom rule.

I have following tables as below:

create table TableA (
ref_num INT
);

create table TableB (
ref_num INT,
text VARCHAR(100),
table_c_ref_num INT,
table_a_ref_num INT
);

create table TableC (
ref_num INT,
text VARCHAR(100)
);

create table TableD (
ref_num INT,
table_b_ref_num INT,
modfifable_column VARCHAR(1),
start_date date,
end_date date
);

Inserting initial values as below:

insert into TableA (ref_num) values (1);
insert into TableC (ref_num, text) values
(1, "Text 1"),
(2, "Text 2"),
(3, "Text 3");
insert into TableB (ref_num, text, table_c_ref_num, table_a_ref_num) values
(1, NULL, 2, 1);
insert into TableD (ref_num, table_b_ref_num, modfifable_column, start_date, end_date) values
(1, 1, 'Y', now(), NULL);

Now I have created this select statement to get wanted behaviour:

SELECT * FROM (
SELECT
tb.ref_num AS ref_num,
tb.table_a_ref_num AS table_a_ref_num,
coalesce(tc.text, tb.text),
coalesce(tc.ref_num, tb.table_c_ref_num) AS table_c_ref_num,
coalesce(td.modfifable_column, 'N') AS modfifable_column
FROM TableB tb
LEFT JOIN TableD td on td.table_b_ref_num = tb.ref_num AND td.end_date IS NULL
LEFT JOIN TableC tc on tc.ref_num = tb.table_c_ref_num
WHERE tb.table_a_ref_num = 1
) as cust
UNION ALL
SELECT * FROM (
SELECT
NULL AS ref_num,
NULL AS table_a_ref_num,
tc2.text AS text,
tc2.ref_num AS table_c_ref_num,
'N' AS modfifable_column
FROM TableC tc2
WHERE tc2.ref_num NOT IN (
SELECT
tb2.table_c_ref_num
FROM TableB tb2
LEFT JOIN TableD td on td.table_b_ref_num = tb2.ref_num AND td.end_date IS NULL
LEFT JOIN TableC tc on tc.ref_num = tb2.table_c_ref_num
WHERE tb2.table_a_ref_num = 1
)
) as def;

I know that I can turn those two inner SELECT statements into views and then combine them with UNION ALL for example. My biggest concern here is that I have to "hard code" table_a_ref_num = 1 into two different places. Because I have to use TableA ref_num in order to get custom values from TableB and default values from TableC. Because at the end TableA ref_num is like “this specific” entity custom rules and default rules.

My question is: Is there a way to wrap my big SELECT clause into one view, where I could use this TableA ref_num value to get results as in my example.
I thought one way would be to group them by TableA ref_num values. E.g.: I select values from TableB by TableA ref_num and then add all the values from Table C which are not present in TableA. Grouping them by TableA ref_num.