Perform join on two tables when values in one table may or may not be encloses in double quotes

user_15358022 0
Viewed 30

I have two tables

1.Employee Table

+-------+----------+-----------------+
| empno | empname  | deptname        |
+-------+----------+-----------------+
| 1     | Ross     | dept1_aniosbgis |
| 2     | Joey     | dept2_aswefbhjk |
| 3     | Chandler | dept3_xwukpgbtr |
| 4     | Monica   | dept1_aniosbgis |
| 5     | Phoebe   | dept1_aniosbgis |
| 6     | Rachel   | dept3_xwukpgbtr |
+-------+----------+-----------------+

2.Bonuses Table

+---------------+-------+
| department    | Bonus |
+---------------+-------+
| dept1_finance | 40000 |
| "dept2_hr"    | 50000 |
| "dept3_tech"  | 60000 |
+---------------+-------+

I want to perform a join on the 2 tables based on the department name column which is the deptname column in employees and department column in bonuses. My issue is that the department column has some rows where the names are enclosed in double quotes and some are not. I need to take substring of the department name and it can be done something like this -

substr(department,1,5)

But in case of double quotes , it has to be done like this

substr(department,2,5)

I want to write an if-else statement to perform the join. But I'm not able to construct the query. I would prefer to use joins ( to maintain the same pattern as with the other tables I have ). The output should look like -

+-------+-------+
| empno | bonus |
+-------+-------+
| 1     | 40000 |
| 2     | 50000 |
| 3     | 60000 |
| 4     | 40000 |
| 5     | 40000 |
| 6     | 60000 |
+-------+-------+

Please help me in framing the query

sql
2 Answers

Use the function TRIM() to remove leading and trailing double quotes (if they exist) from department:

SELECT e.empno, b.bonus
FROM Employee e LEFT JOIN Bonuses b
ON LEFT(TRIM('"' FROM b.department), 5) = LEFT(e.deptname, 5)

This code works for MySql and Postgresql.

user_104988280

one wya is to join on both condition using IN :

select * 
from table1 t1
join tbale2 t2
on t1.deptname in (t2.department, substr(t2.department,2,5) )
user_13674540
Related