Search code examples
mysqlsubquerygroup-concat

How I can pass the results obtained by a query with group_concat(distinct) in another query?

I have a table named university_schools which contains all the courses records of each university and a table university_main_campus which contains records of all the universities. I want all the courses names from another table called 'courses'(which contains all the details including course_name, course_slug etc) of each course within that university.

Here is my query:

SELECT * FROM courses 
WHERE course_id IN (SELECT GROUP_CONCAT(DISTINCT course_id) AS STR_CRSE_IDS 
                    FROM `university_schools` WHERE univ_id = '2289')

I am getting only the first record whereas there should be 11 records.

Please help...

Solution

  • Try this query,

    SELECT courses.*, b.STR_CRSE_IDS FROM courses INNER JOIN (SELECT DISTINCT course_id as STR_CRSE_IDS FROM university_schools WHERE univ_id = '2289') AS b ON courses.course_id = b.STR_CRSE_IDS