with t1 as (
    select
    case
        when course like '%course3%'
            then 1
            else 0
    end as course_3,
    s.staff_id,
    staff_name
from
    staff_tb s, cultivate_tb c
where s.staff_id = c.staff_id)
select
    staff_id,staff_name
from t1
where course_3 = 1

This SQL query is structured with two parts: a Common Table Expression (CTE) (t1) and the final SELECT statement. Let's break down the code step by step:

1. Common Table Expression (CTE) - t1

WITH t1 AS (
    SELECT
        CASE
            WHEN course LIKE '%course3%' THEN 1
            ELSE 0
        END AS course_3,
        s.staff_id,
        staff_name
    FROM
        staff_tb s, cultivate_tb c
    WHERE s.staff_id = c.staff_id
)

Explanation of the CTE:

  • WITH t1 AS (...): This creates a temporary result set called t1 that can be referenced in the outer query.
  • CASE WHEN course LIKE '%course3%' THEN 1 ELSE 0 END AS course_3: This checks whether the course column (from cultivate_tb) contains the string 'course3':If it does, course_3 is set to 1.If it does not, course_3 is set to 0.
  • s.staff_id, staff_name: These are selected from the staff_tb table, which represents information about staff.
  • FROM staff_tb s, cultivate_tb c: This is a cross join between staff_tb (s) and cultivate_tb (c). This is a bit unusual because it doesn’t explicitly specify the relationship between these two tables (which is usually done with JOIN). However, the next line, WHERE s.staff_id = c.staff_id, is what binds the two tables, effectively turning the cross join into an inner join based on the staff_id column in both tables.
  • WHERE s.staff_id = c.staff_id: This ensures that each staff member (staff_tb) is matched with the corresponding record in cultivate_tb where the staff_id is the same. So, the staff will only appear if they have a corresponding entry in cultivate_tb.

2. Outer Query

SELECT
    staff_id, staff_name
FROM t1
WHERE course_3 = 1

Explanation of the outer query:

  • FROM t1: This indicates that the query will be executed on the result of the CTE (t1).
  • WHERE course_3 = 1: This filters the rows where course_3 is 1, meaning the staff members who have 'course3' in the course column of cultivate_tb.

What the Query Does:

  • The CTE (t1) first checks for each staff_id whether their associated course contains the string 'course3'. For each staff, if the course contains 'course3', course_3 is set to 1, otherwise it is set to 0.
  • The outer query then selects the staff_id and staff_name of those staff members who have course_3 equal to 1 (i.e., those who have 'course3' in the course column).