How can we do a simple join like this in Oracle?

Question

How can we do a simple join like this?

I have three tables:

Table 1: Student

UserID
11111111111
22222222222
33333333333
44444444444
55555555555
66666666666
77777777777
88888888888
99999999999

Table 2: Class

ClassID | StartDate
0001    | Apr 2,2018, 9:00 AM
0002    | May 2,2018, 9:00 AM

Table 3: Enroll

UserID      | ClassID
11111111111 | 0001
11111111111 | 0002
22222222222 | 0001
33333333333 | 0002

And I need as result the table bellow:

Expected table

UserID      | ClassID | StartDate
11111111111 | 0001    | Apr 2,2018, 9:00 AM
11111111111 | 0002    | May 2,2018, 9:00 AM
22222222222 | 0001    | Apr 2,2018, 9:00 AM
33333333333 | 0002    | May 2,2018, 9:00 AM
44444444444 | null    | null
55555555555 | null    | null
66666666666 | null    | null
77777777777 | null    | null
88888888888 | null    | null
99999999999 | null    | null

If it’s possible, do this without using UNION.

FURTHER EDIT

Table 3: Enroll (with new 'EnrollStatus' column)

UserID      | ClassID | EnrollStatus
11111111111 | 0001    | Enrolled
11111111111 | 0002    | Enrolled
22222222222 | 0001    | Enrolled
33333333333 | 0002    | Enrolled

Expected table (with new 'EnrollStatus' column)

UserID      | ClassID | StartDate           | EnrollStatus
11111111111 | 0001    | Apr 2,2018, 9:00 AM | Enrolled
11111111111 | 0002    | May 2,2018, 9:00 AM | Enrolled
22222222222 | 0001    | Apr 2,2018, 9:00 AM | Enrolled
22222222222 | null    | null                | null
33333333333 | null    | null                | null
33333333333 | 0002    | May 2,2018, 9:00 AM | Enrolled
44444444444 | null    | null                | null
44444444444 | null    | null                | null
55555555555 | null    | null                | null
55555555555 | null    | null                | null
66666666666 | null    | null                | null
66666666666 | null    | null                | null
77777777777 | null    | null                | null
77777777777 | null    | null                | null
88888888888 | null    | null                | null
88888888888 | null    | null                | null
99999999999 | null    | null                | null
99999999999 | null    | null                | null

I believe that this JOIN is necessary because I have another table (further) with the dates of the year and I need to count how many weeks users have not enrolled in a specific period (filter). Furthermore, need to show what are the weeks that users have not enrolled (by user).

Table 4: Dates (from: SQL working week in Oracle)

WITH DATES AS
(
  SELECT DATE '2017-12-25' + LEVEL -1 dt FROM DUAL CONNECT BY LEVEL <= 500
)
SELECT dt,TO_CHAR(dt,'DY') DAY,TO_CHAR(dt,'WW') WW,TO_CHAR(dt,'IW') IW,
   CASE WHEN TO_CHAR(dt,'D')<TO_CHAR(TO_DATE(TO_CHAR(dt,'YYYY')||'0101','YYYYMMDD'),'D') THEN 
     LPAD(TO_CHAR(dt,'WW')+1,2,'0')
   ELSE 
     TO_CHAR(dt,'WW')
   END MY
FROM dates

Query result

DT                    | DAY | WW | IW | MY
Dec 25, 2017, 3:00 AM | MON | 52 | 52 | 52
Dec 26, 2017, 3:00 AM | TUE | 52 | 52 | 52
Dec 27, 2017, 3:00 AM | WED | 52 | 52 | 52
Dec 28, 2017, 3:00 AM | THU | 52 | 52 | 52
Dec 29, 2017, 3:00 AM | FRI | 52 | 52 | 52
Jan 1, 2018, 3:00 AM  | MON | 01 | 01 | 01
Jan 2, 2018, 3:00 AM  | TUE | 01 | 01 | 01
Jan 3, 2018, 3:00 AM  | WED | 01 | 01 | 01
Jan 4, 2018, 3:00 AM  | THU | 01 | 01 | 01
Jan 5, 2018, 3:00 AM  | FRI | 01 | 01 | 01
Jan 8, 2018, 3:00 AM  | MON | 02 | 02 | 02
Jan 9, 2018, 3:00 AM  | TUE | 02 | 02 | 02
Jan 10, 2018, 3:00 AM | WED | 02 | 02 | 02
Jan 11, 2018, 3:00 AM | THU | 02 | 02 | 02
Jan 12, 2018, 3:00 AM | FRI | 02 | 02 | 02
Jan 15, 2018, 3:00 AM | MON | 03 | 03 | 03
Jan 16, 2018, 3:00 AM | TUE | 03 | 03 | 03
Jan 17, 2018, 3:00 AM | WED | 03 | 03 | 03
Jan 18, 2018, 3:00 AM | THU | 03 | 03 | 03
Jan 19, 2018, 3:00 AM | FRI | 03 | 03 | 03

@Dragonthoughts, I've tried with `LEFT JOIN`, but I missed some details in my original question...so I made corrections and complements. — Igor Ivanov, Jul 03 '18 at 19:29
@a_horse_with_no_name, I have tried this too. Can you please consider my corrected question? — Igor Ivanov, Jul 03 '18 at 19:30

D-Shih · Accepted Answer · 2018-07-05T22:42:19.687

4

You can try to use LEFT JOIN base on Student table

SELECT s.*,e.*,c.* 
  FROM Student s 
  LEFT JOIN Enroll e on s.UserID =e.UserID
  LEFT JOIN Class c on c.ClassID =e.ClassID 
 ORDER BY s.UserID

Sqlfiddle

EDIT

I Saw you edit your question, You seem like want to get Cartesian Product.

You need to CROSS JOIN on Student table and Class then LEFT JOIN

SELECT s.userID,
       e.CLASSID,
       (CASE WHEN e.CLASSID IS NULL then NULL else c.STARTDATE END) STARTDATE,
       e.EnrollStatus
FROM 
Student s 
CROSS JOIN Class c 
LEFT JOIN Enroll e
    on s.UserID =e.UserID AND e.CLASSID = c.CLASSID
ORDER BY s.UserID

Results:

|      USERID | CLASSID |           STARTDATE | ENROLLSTATUS |
|-------------|---------|---------------------|--------------|
| 11111111111 |    0001 | Apr 2,2018, 9:00 AM | EnrollStatus |
| 11111111111 |    0002 | May 2,2018, 9:00 AM | EnrollStatus |
| 22222222222 |    0001 | Apr 2,2018, 9:00 AM | EnrollStatus |
| 22222222222 |  (null) |              (null) |       (null) |
| 33333333333 |  (null) |              (null) |       (null) |
| 33333333333 |    0002 | May 2,2018, 9:00 AM | EnrollStatus |
| 44444444444 |  (null) |              (null) |       (null) |
| 44444444444 |  (null) |              (null) |       (null) |
| 55555555555 |  (null) |              (null) |       (null) |
| 55555555555 |  (null) |              (null) |       (null) |
| 66666666666 |  (null) |              (null) |       (null) |
| 66666666666 |  (null) |              (null) |       (null) |
| 77777777777 |  (null) |              (null) |       (null) |
| 77777777777 |  (null) |              (null) |       (null) |
| 88888888888 |  (null) |              (null) |       (null) |
| 88888888888 |  (null) |              (null) |       (null) |
| 99999999999 |  (null) |              (null) |       (null) |
| 99999999999 |  (null) |              (null) |       (null) |

CORSS JOIN

edited Jul 05 '18 at 22:42

answered Jul 02 '18 at 20:50

D-Shih

44,943
6
31
51

Thank you! But this not works because my original question was some mistakes. Can you please consider my corrected question? – Igor Ivanov Jul 03 '18 at 19:32
Could you give me more detail from your expect result? and what's the relationship on Table 4: Dates – D-Shih Jul 03 '18 at 19:37
D-Shih, As my final result I need a table that shows all users and all classes with the following columns: `UserID | ClassID | StartDate | EnrollStatus | WW | COUNT` *WW indicates the week number in the year So if I have 1000 users and 500 classes, this table should be 500000 rows. ... – Igor Ivanov Jul 05 '18 at 21:00
... Imagine a report that controls the training hours of all employees and one part of this report shows the number of weeks without registrations (enrolls) by user. Furthermore, another column to show what are those weeks (thinking to use `LISTAGG` inside of a new `JOIN` using Dates to link the tables: `Table 4 Dates` x `Expected Table`). – Igor Ivanov Jul 05 '18 at 21:00
... The `COUNT` its to count the number of weeks without registrations (enrolls). – Igor Ivanov Jul 05 '18 at 21:03
@IgorIvanov You need a `CROSS JOIN` on `Student` table and `Class` table http://sqlfiddle.com/#!4/8276e/10 – D-Shih Jul 05 '18 at 21:35
I never used CROSS JOIN before. I'll test this solution, but it seems that will work! :) – Igor Ivanov Jul 05 '18 at 22:17
Yes! Thank you very much! I had to make few changes, but helped me a lot! – Igor Ivanov Jul 10 '18 at 14:13

How can we do a simple join like this in Oracle?

1 Answers1