Complex Select Query In Oracle

Question

I have the following 3 tables 1- EmpLoyees as:

EmpID    EmpName
--------------
100       Adam
101       Smith

2- Shifts as:

ShiftID    ShiftName  FromTime   ToTinme
----------------------------------------
1          Shift1     9 AM        2 PM
2          Shift2     3 PM        5 PM

3-EmpShifts as :

EMPID    SHIFTID    Date   
---------------------------------
100       1,2      01/12/2017     
100       2        02/12/2017 
101       1        01/12/2017   
101       1,2      02/12/2017

I need to make a select Statement to get the data Like that:

EMPID    EmpName       Date          ShiftID       ShiftName   ShiftTimes   
------------------------------------------------------------------------ 
100       Adam         01/12/2017     1             Shift1
100       Adam         01/12/2017     2             Shift2
100       Adam         02/12/2017     2             Shift2
101       Smith        01/12/2017     2             Shift2
101       Smith        02/12/2017     1             Shift1
101      Smith        02/12/2017      2              Shift2

So How Get That.

Have you tried something yet? Don't store CSV in your tables. — Tim Biegeleisen, Sep 11 '17 at 11:18
`EmpShifts.SHIFTID` column have comma separated values? this is not good — Oto Shavadze, Sep 11 '17 at 11:19
The `SHIFTID` column in the `EmpShifts` table does not follow [1NF](https://en.m.wikipedia.org/wiki/First_normal_form) rules since there are not atomic values stored in this field. You must first normalize this table, then you will be able to use simple join clause to get desired result. — krokodilko, Sep 11 '17 at 11:22
You may use the `regexp` to decompose the comma separated value https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle — Radim Bača, Sep 11 '17 at 11:25

score 1 · Answer 1 · answered Sep 11 '17 at 11:25

As mentioned in the comments, this is a lousy data format. Storing lists as strings is bad. Here are some reasons:

Numbers should be stored as numbers, not strings.
Columns with the same name (shiftid) should contain the same thing.
Foreign key references should be properly declared.
String operations are much less efficient than other methods.

That said, you can do what you want, using something like like for the comparison. The following query shows the construct that you need:

select . . .
from empshifts es join
     shifts s
     on ',' || shiftid || ',' like '%,' || s.shiftid || ',%' join
     employees e
     on e.empid = es.empid;

score 0 · Answer 2 · answered Sep 11 '17 at 11:26

I did not run the query but it will work

try this

with shifids as (
select empid,date,regexp_substr(SHIFTID,'[^,]+', 1, level)shifid from EmpShifts 
   connect by regexp_substr(SHIFTID, '[^,]+', 1, level) is not null
   )
select EmpLoyees.empid,EmpLoyees.empname,
shifids.date,shifids.shifid,
Shifts.ShiftID,Shifts.ShiftName
from
EmpLoyees inner join
shifids
on EmpLoyees.empid=shifids.empid
inner join Shifts on Shifts .shiftid=shifids.shifid

score 0 · Answer 3 · answered Sep 11 '17 at 11:39

As all the men said, storing list with comma separated is a bad idea.

But here also a solution INSTR exists:

SELECT 
    E."EmpID",
    E."EmpName",
    ES."Date",
    S."ShiftID",
    S."ShiftName"
FROM EmpShifts ES
INNER JOIN Shifts S ON INSTR(ES."SHIFTID", S."ShiftID", 1, 1) > 0
INNER JOIN EmpLoyees E ON ES."EMPID" = E."EmpID"

Not sure if it's what you want or not, but try it, here is a demo in SQLFiddle.

Complex Select Query In Oracle

3 Answers3

try this