CODEWITHSUNDEEP
mysqlsqloracle-databasejoin
jpiter
jpiter

Reputation: 3

Create view to get values from multiple tables

I thought it is easier to edit this post and give exact example what I meant by that post after all.

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.

Upvotes: 0

Views: 139

Answers (1)

Peter
Peter

Reputation: 1196

I don't fully understand your tables and pseudo code, but based on this limited understanding, my suggestion would be that you start with a query similar to this on:

select 'ta',ta.*,'tb',tb.*,'tc_via_b',tc_via_b.*,'tc_via_a',tc_via_a.*,'td',td.*
from      table_a ta
left join table_b tb       on tb.table_a_ref_num = ta.ref_num
left join table_c tc_via_b on   tc_via_b.ref_num = tb.table_c_ref_num
left join table_c tc_via_a on   tc_via_a.ref_num = ta.ref_num
left join table_d td       on td.table_b_ref_num = tb.ref_num AND td.end_date IS NULL;

This way you will see all lines you want as a first step. In a second step, you should be able to use NVL and CASE to get the data you want. HTH

Upvotes: 1

Related Questions