CODEWITHSUNDEEP
sqloracle-database
Butterfly
Butterfly

Reputation: 19

SQL join and add row values as column names

I need your help.

Given 3 tables

Table name 1: Table1

ID Name
1 Mike
2 John

Table name 2: join1

ID column value
1 job_description manager
1 salary 3000

Table name 3: join2

ID column value
1 Hobby cycling
1 Date of join 12.01.2020

output should join Table1 where ID=1 and join both tables Join1 and Join2 with row as column name "job_description" from join1 and with 2 rows as columns "Hobby" and "salary" from join2 table like so:

ID name job_description Hobby salary
1 Mike manager cycling 3000

Thanks

Upvotes: 0

Views: 323

Answers (2)

DannySlor
DannySlor

Reputation: 4620

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'
ID NAME JOB_DESCRIPTION HOBBY SALARY
1 Mike manager cycling 3000

Fiddle

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

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

Upvotes: 1

Related Questions