How to join vertical and horizontal table together table

Question

I have two table with one of them is vertical i.e store only key value pair with ref id from table 1. i want to join both table and dispaly key value pair as a column in select. and also perform sorting on few keys. T1 having (id,empid,dpt) T2 having (empid,key,value)

select 
    T1.*,
    t21.value,
    t22.value,
    t23.value,
    t24.value

from    Table1 t1
join    Table2 t21 on t1.empid = t21.empid
join    Table2 t22 on t1.empid = t22.empid
join    Table2 t23 on t1.empid = t23.empid

where   
        t21.key = 'FNAME'
    and t22.key = 'LNAME'
    and t23.key='AGE'

Answer 1

The query you demonstrate is very inefficient (another join for each additional column) and also has a potential problem: if there isn't a row in T2 for every key in the WHERE clause, the whole row is excluded.

The second problem can be avoided with LEFT [OUTER] JOIN instead of [INNER] JOIN. But don't bother, the solution to the first problem is a completely different query. "Pivot" T2 using crosstab() from the additional module tablefunc:

SELECT * FROM crosstab(
       'SELECT empid, key, value FROM t2 ORDER  BY 1'
    , $$VALUES ('FNAME'), ('LNAME'), ('AGE')$$  -- more?
   ) AS ct (empid int      -- use *actual* data types
          , fname text
          , lname text
          , age   text);
          -- more?

Then just join to T1:

select *
from   t1
JOIN  (<insert query from above>) AS t2 USING (empid);

This time you may want to use [INNER] JOIN.

The USING clause conveniently removes the second instance of the empid column.

Detailed instructions:

• PostgreSQL Crosstab Query