SQL join two tables by modifying on columns-CodePudding

I have 3 tables on PostgreSQL:

SPENDINGS:

store      |spending
----------- ---------
0000700551 |   $75

STORE:

store  | zip_code
------- ---------
700551 | XXP PDD

CUSTOMER:

id  | zip_code
---- ----------
002 | XXPPDD

I would love to join these tables like this:

right(SPENDIGNS.store, 6) = STORE.store and     
trim(STORE.zip_code) = CUSTOMER.zip_code.

How can I do that could you help me out please?

CodePudding user response：

trim(store.zip_code) is not good for the job because the whitespace is within the zip code text. Use replace(store.zip_code, ' ', '') instead. right(spendings.store, 6) does not seem safe to me too. Better use ltrim(spendings.store, '0') to remove leading zeroes. SQL Fiddle

select * 
  from spendings sp 
  join store st on ltrim(sp.store, '0') = st.store
  join customer cu on cu.zip_code = replace(st.zip_code, ' ', '');

BTW your data design does need improvement.

CodePudding user response：

try something as follows (you can use what ever join you would like to in place of inner)

select spendings.spending, store.store,customer.zipcode 
from spendings inner join store on right(SPENDIGNS.store, 6) = STORE.store 
inner join customer on trim(STORE.zip_code) = CUSTOMER.zip_code