CODEWITHSUNDEEP
sqlpostgresqljoin
ddd
ddd

Reputation: 5029

How to join with different fields based on condition

I need to join two tables door_ctrlr_fact and equip_store_dim in my query. 

select * from door_ctrlr_fact d
join equip_store_dim e
on d.door_id = e.tech_nbr

There are a small set of the data with special door_id, for example WM1-1003-072. The third field in this string has leading zero which needs to be removed before joining. So the query for this subset of data is:

select * from door_ctrlr_fact d
join equip_store_dim e
on split_part(d.door_id, '-', 1) = split_part(e.tech_nbr, '-', 1)
and split_part(d.door_id, '-', 2) = split_part(e.tech_nbr, '-', 2)
and trim(leading '0' from split_part(e.door_id, '-', 3)) = trim(leading '0' from split_part(e.tech_nbr, '-', 3))
where door_id like 'WM%'

In order to handle all cases, I could just union the two queries based on what door_id looks like.

For example, in table door_ctrlr_fact:

door_id       cycle_cnt
A10003        500
WM4-103-070   1000
B200384       2000

In table equip_store_dim

tech_nbr       store_id
A10003         S001
WM4-103-70    S002
B200384        S004

After the join, the result should be:

door_id      cycle_cnt       store_id
A10004        500             S001
WM4-103-70    1000            S002
B200384       2000            S004

I wonder if there is better way to do it though. Can I use just one query and join the fields differently based on different format of door_id

Upvotes: 0

Views: 358

Answers (1)

Joakim Danielson
Joakim Danielson

Reputation: 51945

I used the REGEXP_REPLACE function to modify door_id in the join, I use groups in the regex to only match a '-0' combo after the second hyphen

SELECT door_id, cycle_cnt, store_id
FROM door_ctrlr_fact
JOIN equip_store_dim ON tech_nbr = REGEXP_REPLACE(door_id, '(.*-.*)(-0)(.*)', '\1-\3')

Upvotes: 2

Related Questions