0

I'm trying to join a few tables to produce a list of classes that have prerequisite. All is well except for the fact that I need the class names for BOTH the prerequisites and the original class.

So far i have

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

which gives me (you can already see an issue the course_name is in there twice[no idea how to handle it otherwise])

  • the course name (okay good)
  • the class (yep)
  • the course name again (wait that's the same name!)
  • the prerequisite (good)

Ignore the format just note that the course names or the prerequisites corresponds only with the original course not the prerequisites.

My issue is that I cant get the names to match up.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
wat
  • 69
  • 2
  • 10

1 Answers1

6

Use aliases and make sure to always include the table name in your field list:

SELECT
    c.course_name,
    c.course_number,
    p.course_name AS prereq_course_name,
    p.prereq
FROM rearp.course AS c, rearp.prereq AS p
WHERE c.course_number = p.course_number

Also, please use meaningful table alias names instead of single letters, and try using actual joins instead of implicit joins. If you are using MySQL, the join will be a cross join, which is inefficient and unnecessary.

EDIT: added commas in the field list...

siride
  • 200,666
  • 4
  • 41
  • 62
  • 3
    **BTW**: This is an actual `INNER JOIN` but with a different syntax, it is ANSI SQL-89 join syntax. But as you said it is not recommended. If you remove the where clause it would be cross join. See [this](http://stackoverflow.com/questions/13289546/which-query-is-the-correct-way/13289653#13289653) for more details. – Mahmoud Gamal Nov 16 '12 at 04:51
  • Oh yeah, that's right. I forgot that in MySQL INNER and CROSS are the same if there's no join condition. Stupid syntax, really, but it is what it is. – siride Nov 16 '12 at 04:52
  • this quey has an issue with p still trying to find out why – wat Nov 16 '12 at 04:53
  • @wat: it's probably because I forgot the commas. – siride Nov 16 '12 at 04:53
  • ERROR at line 4: ORA-00904: "P"."COURSE_NAME": invalid identifier hmmm – wat Nov 16 '12 at 04:55
  • SELECT c.course_name, c.course_number, p.course_name AS prereq_course_name, p.prereq * ERROR at line 1: ORA-00904: "P"."COURSE_NAME": invalid identifier – wat Nov 16 '12 at 05:00
  • Oracle apparently requires that you use the `AS` keyword when defining table aliases. Try putting `AS` in your `FROM` clause. – siride Nov 16 '12 at 05:02
  • odd now its saying its not ended properly in respect to the as – wat Nov 16 '12 at 05:06
  • @wat the syntax is correct. You must have copied it wrong or added something else. Try trimming the query down until you get something that works and then add stuff back until it breaks, figure out what made it break and then correct the issue. – siride Nov 16 '12 at 05:07
  • @wat: next time either copy it verbatim, or make sure to understand what's going on so that you know enough to deal with your version. I cannot be responsible for fixing bugs in code that I can't see. – siride Nov 16 '12 at 05:12