SQL join and add row values as column names

You can join all three tables according to your criteria. For example:

select a.id, a.name, b.value, c.value
from table1 a
join join1 b on b.id = a.id and b.column = 'job_description'
join join2 c on c.id = a.id and c.column = 'Hobby'
where a.id = 1

As a side note, the table names (join1, join2) can be a bit confusing as entity names. The column names (column, value) may be too generic, but this is up to your database design.

EDIT

For the updated question, you want to retrieve multiple row values of the secondary table for the same row in the main table. The typical solution is using aggregations or subqueries. Using the latter the query can take the form:

select a.id, a.name, 
  (select value from join1 b where b.id = a.id
     and b.column = 'job_description') as job_description,
  c.value as hobby,
  (select value from join1 b where b.id = a.id
     and b.column = 'salary') as salary
from table1 a
join join2 c on c.id = a.id and c.column = 'Hobby'
where a.id = 1

select  id
       ,t.name
       ,"'job_description'" as job_description
       ,t3."value"          as Hobby
       ,"'salary'"          as Salary
from    t2
pivot   (max("value") for "column" in('job_description',  'salary')) p join t using(id) join t3 using(id) 
where   "column" = 'Hobby'

Fiddle