CODEWITHSUNDEEP
sqloracle-database
Mr.Burns
Mr.Burns

Reputation: 87

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

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

Upvotes: 0

Views: 293

Answers (4)

REW
REW

Reputation: 1247

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 || '|');

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 754280

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.

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171471

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

Upvotes: 1

zebediah49
zebediah49

Reputation: 7611

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.

Upvotes: 6

Related Questions