How to use Delimited data in WHERE condition during Join Query?

Question

Lets say i have two tables EMPLOYEE and INCHARGE_ROOM , EMPLOYEE.INCHARGE_ROOMS has | delimted data of INCHARGE_ROOM.ID

EMPLOYEE

ID      NAME      INCHARGE_ROOMS
1        K           1|2|3|4
2        S           2|4|5

INCHARGE_ROOM

INCHARGE_ROOM_ID      INCHARGE_ROOM_NAME
1                                        RED
2                                       BLUE
3                                      YELLOW
4                                       ORANGE
5                                       BLACK

If i want to

SELECT ALL THE ROOMS FOR WHICH K IS INCHARGE.

The Expected result is

NAME                         INCHARGE_ROOM
K                                        RED
K                                        BLUE
K                                        YELLOW
K                                        ORANGE

Answer 1

I tried to do this once; it didn't work well.

SQL is designed to have tables linking together, so instead of the incharge_rooms column, it should have another table incharge_rooms(employee_id,room_id). (primary key is on both columns)

Then you just join them together to get whatever you want.

Answer 2

select e.NAME, ir.INCHARGE_ROOM_NAME as INCHARGE_ROOM
from INCHARGE_ROOM ir
inner join EMPLOYEE e on ir.INCHARGE_ROOM_ID  like INCHARGE_ROOMS + '|%'
    or ir.INCHARGE_ROOM_ID  like '%|' + INCHARGE_ROOMS + '|%'
    or ir.INCHARGE_ROOM_ID  like '%|' + INCHARGE_ROOMS

Answer 3

This is a classic example of how not to structure the data. Bill Karwin's book 'SQL AntiPatterns' calls it JayWalking.

Your table design should be fixed. If you can't fix it, then you have to use DBMS-specific techniques to undo the damage done by the broken schema design.

You need 3 tables, not 2:

Employee (EmployeeID, Name)
InChargeRoom (InChargeRoomID, InChargeRoomName)
EmpInCharge (EmployeeID, InChargeRoomID)

Then you can do a simple 3-way join to get the required result trivially.

Answer 4

Assuming you can't change the structure (which many people can't because of legacy code or third-party code):

select 
  emp.name as NAME
  , room.incharge_room_name as INCHARGE_ROOM
from employee emp
inner join incharge_room room 
  on ('%|' || room.incharge_room_id || '|%' like '|' || emp.incharge_rooms || '|');