Join multiple values to virtual columns

Question

I have SQL table1 with value 'value1' in column Type. Then I have second table table2 in column Name 'value1'.

In table2 in row with name 'value1' (unique data in this table), there are more column: color -> 'red', icon -> 'checkmark', type -> 'something'. How to join queries to put values from joined table to main table? I've searched for it, but I didn't find this type of joining.

I want select from table table1 + data from table2

Thanks!

Answer 1

Here is an example for your usecase. First build up your structure (like in task description):

CREATE TABLE table1(
  type varchar(20)
);

CREATE TABLE table2(
  name varchar(20),
  color varchar(20),
  icon varchar(20),
  type varchar(20)
);

Then insert some sample data:

INSERT INTO table1 VALUES ('value1');
INSERT INTO table1 VALUES ('value2');

INSERT INTO table2 VALUES ('value1','red','home','type1');
INSERT INTO table2 VALUES ('value1','blue','faq','type2');
INSERT INTO table2 VALUES ('value2','green','disclaimer','type3');

And now your join statement for inserting the data (in my example just the type of table2):

INSERT INTO table1
SELECT t2.type FROM
table1 t1
INNER JOIN table2 t2 ON t1.type = t2.name
WHERE t1.type = 'value1'

You can change the Where clause for your usecase to insert different types of table2 into table1.

If just want to show the additionally data from table2 then you have to make a union all:

SELECT DISTINCT(result.outColumn) FROM 
(
SELECT tab1.type AS outColumn FROM table1 AS tab1
UNION ALL
 SELECT t2.type AS outColumn FROM
    table1 t1
    INNER JOIN table2 t2 ON t1.type = t2.name
    WHERE t1.type = 'value1'
) AS result

If you just want to show a large row of whole data from table1 and table2 then just use (then you get all columns):

 SELECT * FROM
    table1 t1
    INNER JOIN table2 t2 ON t1.type = t2.name
    WHERE t1.type = 'value1'

I hope it helps.