Subquery Matching

[joshmac]

I am trying to do a check to see if a student meets the prerequisite before they can register for a course section.

CourseSecID = 27 (ID of the course section the student wants to register for.)

What I am doing is querying the student's acad cred records to see if any of the courses matches the prereq that is connected to the course they are wanted to register for. If it matches, then they have met the prereq and can then register for the course section. Below is my query and any help or direction is greatly appreciated. The following query does not return any results, so I need help in figuring out what I am doing wrong.

SELECT acadCred.stuAcadCredID FROM stu_acad_cred acadCred WHERE acadCred.courseCode 
IN(SELECT crse.preReq FROM course crse LEFT JOIN course_sec sect ON crse.courseID = sect.courseID 
WHERE sect.courseSecID = '27')
GROUP BY acadCred.stuID,acadCred.courseCode

[cereal]

What kind of values are returned by crse.preReq in the sub query: integers, CSV?

If CSV then the IN() clause is not the correct solution, because it can return something like this:

IN('2,3', '11,17,20', '5')

In order to work each value should be separated, not grouped by the row result, as in the previous example:

IN('2','3','11','17','20','5')

If this is the case, then you could use CONCAT_WS() in the subquery and FIND_IN_SET() instead of the IN() clause. For example:

SELECT FIND_IN_SET(21, (SELECT CONCAT_WS(',', 3, 12, 17, 21, 26) FROM dual)) AS result;

+--------+
| result |
+--------+
|      4 |
+--------+

Where 4 stands for the position in the subquery result list: anything different from 0 means a match.

Links:

• http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_find-in-set
• http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat-ws
• http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#function_in

If this is not the case then, please, provide some extra information about the tables and the result sets.

[joshmac]

crse.preReq returns a string which is the course name (i.e. SCIEN-1001).

[cereal]

Ok, as long acadCred.courseCode uses the same format of crse.preReq, your query should work fine. Could you show the table structures and some data?

[joshmac]

Thanks @cereal for the help. It is sorted now.