CODEWITHSUNDEEP
sql
wat
wat

Reputation: 69

SQL ambiguous columns

Im working with two tables here.

enter image description here

The idea to take these tables and display the course number, course name, prerequisite, and prerequisite name.

However as you can see I have to deal with ambiguity.

So far I ran this query

SELECT c.course_name, p.course_number, course_name AS "prereq_course",prereq
FROM rearp.course  c, rearp.prereq  p
WHERE c.course_number = p.course_number;

and got this...

enter image description here

Looks like all is good except you may notice that the course name for the prerequisite is just a copy of the course.

How would I fix this issue?

Upvotes: 0

Views: 69

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

I'm guessing that you're wanting to look up the prerequisite course and get it's name.

You need to join back to the course table a second time:

SELECT c.course_name, p.course_number, c2.course_name AS "prereq_course",p.prereq
FROM
   rearp.course  c
      inner join
   rearp.prereq  p
      ON c.course_number = p.course_number
      inner join
   rearp.course c2
      on
         p.prereq = c2.course_number

(I've also switched to ansi join syntax, rather than the , style)

Upvotes: 3

Related Questions