Converting a Join Query into a Sub Query

Question

I'm attempting to write a sub query that wold accomplish the same results as the join query shown below.

SELECT Department_to_major.DNAME
FROM Department_to_major
INNER JOIN Course
ON Department_to_major.Dcode = Course.OFFERING_DEPT
WHERE Course.COURSE_NAME LIKE '%INTRO%'
GROUP BY Department_to_major.DNAME

However each attempt has produced errors.

Is there a way to write this as a sub query?

Answer 1

Try the below query. I am assuming that you have used "GROUP BY" clause to make DNAME field unique.

SELECT DISTINCT(DNAME)
FROM Department_to_major
WHERE Dcode IN (SELECT OFFERING_DEPT
            FROM Course
            WHERE COURSE_NAME LIKE '%INTRO%');