CODEWITHSUNDEEP
mysqlview
Ben
Ben

Reputation: 1011

Create a table with some columns that are views of other tables in MySQL?

I would like to create a table with data but I would like one of the columns to be data from another table (like a foreign key) but I don't require this kind of db integrity, I just want to have the values from table b showing in table a.

Question: Is there a way to create a partial view like I describe above?

Thanks in advance.

SQL Code:

CREATE TABLE table1 (
data_column_1 int(10)
data_column_2 int(10)
data_column_3 int(10)
view_column_2 varchar(250)
/* from on table 2
   updated with a trigger? that reads data_column_2
   and based on the value in data_column_2, updates the value in view_column_2
   (like a vlookup like in excel)]
*/
view_column_3 varchar(250)
/* from on table 3
   updated with a trigger? that reads data_column_3
   and based on the value in data_column_3, updates the value in view_column_3
   (like a vlookup like in excel)
*/
)

Upvotes: 0

Views: 550

Answers (1)

peterm
peterm

Reputation: 92785

If I understand correct you just need two reference tables and a SELECT with JOIN. Your CREATE TABLE statements for three tables may look like this

CREATE TABLE table1 (
    data_column_1 int(10),
    data_column_2 int(10),
    data_column_3 int(10)
);
CREATE TABLE table2 (
    data_column_2 int(10),
    view_column_2 varchar(250)
);
CREATE TABLE table3 (
    data_column_3 int,
    view_column_3 varchar(250)
);

Let's assume that we have following sample data 

table 1
------------
10    1    3
20    2    2
30    3    1

table2
------------
1    Apple
2    Banana
3    Orange

table3
------------
1    Red
2    Green
3    Yellow

Then this SELECT will give what you want

SELECT t1.data_column_1,
       t1.data_column_2,
       t1.data_column_3,
       t2.view_column_2,
       t3.view_column_3
  FROM table1 t1 LEFT JOIN
       table2 t2 ON t1.data_column_2 = t2.data_column_2 LEFT JOIN
       table3 t3 ON t1.data_column_3 = t3.data_column_3

We use LEFT JOIN assuming that not for all values in data_column_2 and data_column_3 there are reference values in table2.view_column_2 and table3.view_column3 respectively. If you always have respective values in reference tables or you want filter out those that don't have them from your resultset then use INNER JOIN instead.

Output:

+---------------+---------------+---------------+---------------+---------------+
| data_column_1 | data_column_2 | data_column_3 | view_column_2 | view_column_3 |
+---------------+---------------+---------------+---------------+---------------+
|            10 |             1 |             3 | Apple         | Yellow        |
|            20 |             2 |             2 | Banana        | Green         |
|            30 |             3 |             1 | Orange        | Red           |
+---------------+---------------+---------------+---------------+---------------+

If you want to you may wrap it up in a VIEW

CREATE VIEW consolidated_table
AS
SELECT t1.data_column_1,
       t1.data_column_2,
       t1.data_column_3,
       t2.view_column_2,
       t3.view_column_3
  FROM table1 t1 LEFT JOIN
       table2 t2 ON t1.data_column_2 = t2.data_column_2 LEFT JOIN
       table3 t3 ON t1.data_column_3 = t3.data_column_3

And then use it like this

SELECT * 
  FROM consolidated_table
 WHERE data_column_1 < 20

Output:

+---------------+---------------+---------------+---------------+---------------+
| data_column_1 | data_column_2 | data_column_3 | view_column_2 | view_column_3 |
+---------------+---------------+---------------+---------------+---------------+
|            10 |             1 |             3 | Apple         | Yellow        |
+---------------+---------------+---------------+---------------+---------------+

Upvotes: 1

Related Questions