Facing trouble related to column referencing.detailed problem below Say I have 2 tables Table Metadata and Table Transactions

Table Metadata has the following information regarding columns

Attribute   Value   desc
EmpID      123456   employee of the month
Name        XYZ     defaulter

The Table Structure of the Transactions table is as follows:

EmpID   Name  Type  Title
0       ABC    A        …
1       DEF    A        …
2       GHI    A        …
3       JKL    A        …
12345   XYZ    A        …

I want to be able to join such that I get the resultset as

EmpID   Name  Type  Title  desc
0       ABC    A        …
1       DEF    A        …
2       XYZ    A        …  defaulter
3       JKL    A        …
12345   PQR    A        …  employee of the month

How can I achieve this? I am using postgresql. So I do understand that I have to use information schema, but confused about the joins. Any help?


This is what you want,

SELECT t.*, coalesce( m1.desc, m2.desc ) AS desc
FROM transactions AS t
LEFT OUTER JOIN metadata AS m1
  ON (m1.attribute = 'Name' AND t.name = m1.value )
LEFT OUTER JOIN metadata AS m2
  ON (m2.attribute = 'EmpID' AND t.empid = m2.value );

What we do is join twice once for each join condition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.