MySQL Forums
Forum List  »  Views

Create view using multiple tables
Posted by: Priit Pärl
Date: December 18, 2018 02:51AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Create view using multiple tables
761
December 18, 2018 02:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.