SQL Query using INNER JOIN

Question

Write a query that displays the student firstname, lastname, course number, and course name for all students taking classes – use an INNER Join. Label the output columns Student First, Student Last, Course Number, and Course Name. You should have 7 rows.

is the question in my lab.

there are three tables students,courses,registration

I can get the names of the students that are registered in a course with

select firstname,lastname from students
inner join on registration students.studentid=registration.studentid

but when i try to get the other data the teacher wants returned from the courses table it doesnt work I tried a million things but what makes sense to me is

select firstname,lastname,coursenumber,coursename from students,courses
inner join registration on students.studentid=registration.studentid

but it gives me an error unknown column students.studentid in on clause.

is this right: ` join on registration students.studentid=registration.student.id` I think you have typos, can you correct these? — Michael
– Michael, Commented Nov 16, 2012 at 2:59
sorry forgot to add that columns in students are studentid,firstname,lastname,major,admitdate,graddate,gender,dob — dsquaredtech
– dsquaredtech, Commented Nov 16, 2012 at 3:00

Michael Berkowski · Accepted Answer · 2012-11-16 03:07:28Z

2

You were very close, missing the joining condition between registration and courses. You have an odd mix of implicit and explicit INNER JOINs. Your join into courses should be another INNER JOIN which is joined through registration to students.

SELECT
  firstname, /* <-- don't forget to label your columns as required */
  lastname,
  coursenumber,
  coursename 
FROM
  students
  /* `students` map into courses via many-to-many relation in `registration` */
  INNER JOIN registration on students.studentid = registration.studentid
  /* Inner join through `registration` into `courses` */
  INNER JOIN courses ON registration.courseid = courses.courseid

And don't forget your column aliases to satisfy the column output naming requirements. Use the AS keyword in your SELECT list. I'll leave that part of the assignment for you to solve.

Label the output columns Student First, Student Last, Course Number, and Course Name

edited Nov 16, 2012 at 3:07

answered Nov 16, 2012 at 3:02

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dsquaredtech Over a year ago

this gives me an error unknown column 'courses.courseid' in 'on clause'

Michael Berkowski Over a year ago

@dsquaredtech Not possible unless you misspelled it or you used a table alias like INNER JOIN courses AS c ON...

Michael Berkowski Over a year ago

In your comment above, you said the cols in courses were colums in courses courseid,coursename You did not mention a coursenumber column as in your SELECT list though..

dsquaredtech Over a year ago

aha i'm an idiot that's probably it it's not courseid it's coursenumber

Michael Berkowski Over a year ago

@dsquaredtech as both registration.coursenumber and courses.coursenumber? Substitute the correct column names in the join's ON clause and you should be in business.

StackOverflowed · Accepted Answer · 2012-11-16 03:02:14Z

0

select firstname,lastname,coursenumber,coursename from students
inner join registration on students.studentid=registration.studentid
JOIN courses  ON courses.courseid = registration.courseid

You mentioned courses in your query but you didn't join it to anything.

answered Nov 16, 2012 at 3:02

StackOverflowed

5,98510 gold badges63 silver badges122 bronze badges

2 Comments

dsquaredtech Over a year ago

I keep trying these queries and get an error similiar to what I was getting with select firstname,lastname,coursenumber,coursename from students,courses inner join registration on students.studentid=registration.studentid except now the unknown column is courses.courseid in the on clause

StackOverflowed Over a year ago

Glad your question got answered and congrats on joining S/O! In the future, if you have any more MySQL related questions, what would help would be to run SHOW CREATE TABLE _tablename_ for each table involved in your queries.

Collectives™ on Stack Overflow

SQL Query using INNER JOIN

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related