CODEWITHSUNDEEP
mysqlsqlsubquery
ss424
ss424

Reputation: 23

Subselect within select statement returning null

I'm trying to list the name of courses and their prereqs (if there are any) in a university database. The schema for these tables look like this

course(course_id, title, dept_name, credits)
prereq(course_id, prereq_id)

This is my query

SELECT title, (SELECT title
              FROM course NATURAL JOIN prereq c2
              WHERE c1.prereq_id = c2.course_id)
FROM course NATURAL LEFT JOIN prereq c1;

My results are returning null for all values of the second select statement. I have a feeling it has something to do with the value of c1 not being known at the time of the second select statement. Is there a way around this or a better query that can be used?

Upvotes: 2

Views: 396

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

I'm trying to list the name of courses and their prereqs (if there are any) in a university database.

You can do this instead:

SELECT 
  c1.title "Course Title",
  IFNULL(c2.title, "Has No Preqs") "Preq Course Title"
FROM course c1
LEFT JOIN prereq p ON p.course_id = c1.course_id
LEFT JOIN course c2 ON p.prereq_id= c2.course_id

SQL Fiddle Demo

But this won't work in case there are many prerequisite courses for any course.

In this case you have to use GROUP_CONCAT as @danihp pointed out in his answer like so:

SELECT 
  c1.title "Course Title",
  GROUP_CONCAT(IFNULL(c2.title, "Has No Preqs")) "Preq Course Title"
FROM course c1
LEFT JOIN prereq p ON p.course_id = c1.course_id
LEFT JOIN course c2 ON p.prereq_id= c2.course_id
GROUP BY c1.title ;

SQL Fiddle Demo

Upvotes: 1

dani herrera
dani herrera

Reputation: 51715

You are looking for group_concat aggregation function:

SELECT 
   title,  
   group_concat( distinct c2.title ) as prereqs
FROM course 
NATURAL LEFT JOIN prereq c1
left outer join course c2 on c1.course_id = c2.course_id
group by course.course_id;

Upvotes: 1

Related Questions