SQL query to fetch org level data

Question

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

Note, your question works, but showing actual CREATE TABLE scripts and providing sample data as (properly formatted) INSERT scripts would be much better. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 15, 2024 at 22:36

Erwin Brandstetter · Accepted Answer · 2024-07-15 23:20:14Z

1

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 ..

answered Jul 15, 2024 at 23:20

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query to fetch org level data

1 Answer 1

Comments

Your Answer

Hot Network Questions

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

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

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related

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