Creating a subquery to retrieve data from more than one table

I have code as shown below; I need to search new variables I created as cour and year in my stored procedure

SELECT 
    ,s.kid, ep.exper_id, ep.exper_name, mp.md_name, 
    mo.md_option_id, kk.external_id,
    mv.completed_id,
    COUNT(ce.times_observed) AS countoftimes,
    CASE
        WHEN kid = '7180' THEN '044986'
        WHEN kid = '7800' THEN '044984' 
    END AS cour,
    CASE 
        WHEN CE.yid = '2024' THEN '2023-24'
        WHEN CE.yid = '2023' THEN '2022-23'  
    END AS [year]
FROM
    ks.reqe_md_pool mp, ks.reqe_md_options mo, 
    ks.reqe_md_values mv, ks.reqe_experiences_pool ep,
    ks.reqe_completed_experiences ce
LEFT JOIN
    ks.oasis_person kk ON kk.person_id = ce.student_id
LEFT JOIN  
    ks.oasis_schedule s ON ce.schedule_id = s.schedule_id, 
    ks.reqe_list_version lv 
WHERE 
    ep.exper_id = '1492'
    AND mv.md_id = mp.md_id 
    AND ce.exper_id = ep.exper_id 
    AND ce.completed_id = mv.completed_id 
    AND mv.md_option_id = mo.md_option_id 
    AND ce.list_version_id = lv.list_version_id 
GROUP BY 
    s.did, s.cid, s.lid, ep.exper_id, ep.exper_name,
    mp.md_id, mp.md_name, mo.md_option_id, mo.md_option_order, 
    mo.md_option_name, ce.student_id, mv.text_answer, mv.md_value_id,
    mv.completed_id, CE.YID, kk.external_id         

How can I search for year and cour in stored procedure created in SQL?

I tried to do something like this:

SELECT * 
FROM
    (*code at the top* ) 
WHERE
    cour = @cour
    AND year = @year

Then I tried cross apply, which worked with year, but didn't work with cour, because that able is in left join

SELECT 
    ,s.kid, ep.exper_id, ep.exper_name, mp.md_name, 
    mo.md_option_id, kk.external_id,
    mv.completed_id
 
FROM
    ks.reqe_md_pool mp, ks.reqe_md_options mo, 
    ks.reqe_md_values mv, ks.reqe_experiences_pool ep,
    ks.reqe_completed_experiences ce
LEFT JOIN
    ks.oasis_person kk ON kk.person_id = ce.student_id
LEFT JOIN  
    ks.oasis_schedule s ON ce.schedule_id = s.schedule_id, 
    ks.reqe_list_version lv 
   CROSS APPLY (SELECT 
       case WHEN kid='7180' then '044986'
            WHEN kid='7800' then '044984'
 
           END AS UCFCOURSEID)
               C(UCFCOURSEID)
WHERE 
    ep.exper_id = '1492'
    AND mv.md_id = mp.md_id 
    AND ce.exper_id = ep.exper_id 
    AND ce.completed_id = mv.completed_id 
    AND mv.md_option_id = mo.md_option_id 
    AND ce.list_version_id = lv.list_version_id 
GROUP BY 
    s.did, s.cid, s.lid, ep.exper_id, ep.exper_name,
    mp.md_id, mp.md_name, mo.md_option_id, mo.md_option_order, 
    mo.md_option_name, ce.student_id, mv.text_answer, mv.md_value_id,
    mv.completed_id, CE.YID, kk.external_id         

The rest is where values and group by

One way you can make this easier is to utilise apply which provides the derived column you can then both include in your select list and reference in your where clause.

Here is an example:

select ..., cour, ...
from ...
cross apply (
  select case when kid='7180' then '044986' when kid='7800' then '044984' end
)c(cour)
where ...
  and cour = @cour
group by...