sqlpostgresqlpostgresql-9.4

SQL query to fetch org level data

I need to derive a few fields based on below input data. I have 2 input tables:

Table 1:

badge l1_mgr_id l2_mgr_id l3_mgr_id l4_mgr_id l1_mgr_name l2_mgr_name l3_mgr_name l4_mgr_name cost_center
12 888 777 666 555 A B C D 123
666 444 333 222 111 E F G H 125
555 900 800 700 600 I J K L 127
222 38 48 58 68 N O P Q 123
111 90 110 130 150 R S T U 126

Table 2:

cost_center domain
123 workplace
125 PI
127 space
126 blockchain

Based on the condition (pseudo-code):

"if l3_mgr_id=badge then fetch the details of the field(l3_mgr_id,l3_mgr_name) corresponding to it, if l4_mgr_id=badge then fetch the details corresponding to ite(l4_mgr_id,l4_mgr_name)."

For cost center need to find based on both input table for l3 and l4

Expected output:

l3_mgr_id l3_mgr_name l3_cost_center l4_mgr_id l4_mgr_name l4_cost_center
666 G PI 555 L space
222 P workspace 111 U blockchain

I am not getting the expected output.
I have tried below query but it's giving me no results with above data:

select distinct 
                CASE WHEN t1.l3_mgr_id = t1.badge THEN t1.l3_mgr_id ELSE NULL END AS l3_mgr_id,
                CASE WHEN t1.l3_mgr_id = t1.badge THEN t1.l3_mgr_name ELSE NULL END AS   
                l3_mgr_name,
                CASE WHEN t1.l4_mgr_id = t1.badge THEN t1.l4_mgr_id ELSE NULL END AS l4_mgr_id,
                CASE WHEN t1.l4_mgr_id = t1.badge THEN t1.l4_mgr_name ELSE NULL END AS 
                l4_mgr_name,t2.cost_center FROM test t1
left join test t2
on t1.cost_center=t2.cost_center
Solution

  • This produces your desired result:

    SELECT t1.l3_mgr_id, l3.l3_mgr_name, l3c.domain AS l3_cost_center
     , t1.l4_mgr_id, l4.l4_mgr_name, l4c.domain AS l4_cost_center
FROM   t1
JOIN   t1      l3  ON l3.badge = t1.l3_mgr_id
LEFT   JOIN t2 l3c ON l3c.cost_center = l3.cost_center
JOIN   t1      l4  ON l4.badge = t1.l4_mgr_id
LEFT   JOIN t2 l4c ON l4c.cost_center = l4.cost_center;

    fiddle

    Your relational design and your ancient Postgres 9.4 don't seem ideal ..