• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

SQL Join Statement

Hi, I have three tables and I am trying to create a select statement whereby I can select all the student records from  tbl_students and display the class_name that is stored in the tbl_class_names. The class_id and class name are stored in separate tables.

tbl_Students
Student_Number            Class_ID                  Lecture_Room
5                                  18                             5
6                                  15                             9

tbl_Class

Class_ID      Class_ID
18            55            
15            76

tbl_Class_Names
Class_ID      Class_Name
55            Science      Y2      
76            Math Y3      
the result I am looking for would be this

Student_Number            Class_Name                        Lecture_Room
5                                  Science Y2                  5
6                                  Math Y3                        9
I think I need some sort of join statement perhaps?
0
jay_eire
Asked:
jay_eire
  • 7
  • 4
1 Solution
 
Terry WoodsIT GuruCommented:
This looks like a homework/assignment question... We can still help, but it will be in the style of guidance rather than an outright answer.
0
 
Terry WoodsIT GuruCommented:
Joins generally look like this:
select table_a.field1, table_b.field2
from table_a
join table_b on table_b.id = table_a.b_id

Open in new window

If sometimes there is no row in table_b for a row in table_a, but you still want to include data from table_a in the result, then you need to use a left join
0
 
jay_eireAuthor Commented:
thanks for reply TerryAtOpus, its not a homework assignment I'm afraid, it is a genuine query.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Terry WoodsIT GuruCommented:
There's documentation on joins here: http://dev.mysql.com/doc/refman/5.0/en/join.html
0
 
Terry WoodsIT GuruCommented:
The tbl_Class table as you show it has two Class_ID columns; what's going on there? I don't think you can have two columns in a table with the same name.
0
 
Terry WoodsIT GuruCommented:
This is pretty much what you want, but as per my previous comment, I believe there's something wrong with the column names in the tbl_Class table that we'll need to sort out before the query will work.
select tbl_Students.Student_Number, tbl_Class_Names.Class_Name, tbl_Students.Lecture_Room
from tbl_Students
join tbl_Class on tbl_Class.Class_ID = tbl_Students.Class_ID
join tbl_Class_Names on tbl_Class_Names.Class_ID = tbl_Class.Class_ID

Open in new window

0
 
jay_eireAuthor Commented:
sorry that is an error on my part the correct format is

tbl_Class

Class_ID      CN_ID
18            55            
15            76


tbl_Class_Names
CN_ID      Class_Name
55            Science      Y2      
76            Math Y3
0
 
Terry WoodsIT GuruCommented:
Ok, this will hopefully do it:
select tbl_Students.Student_Number, tbl_Class_Names.Class_Name, tbl_Students.Lecture_Room
from tbl_Students
join tbl_Class on tbl_Class.Class_ID = tbl_Students.Class_ID
join tbl_Class_Names on tbl_Class_Names.CN_ID = tbl_Class.CN_ID

Open in new window


Are you sure the Lecture_Room column is in the students table? That would be weird...
0
 
jay_eireAuthor Commented:
thank you for your help on this, I will give it a go now.

There is a few fields that do not make sense. It is a bit of a mess that I have inherited I am trying to clean up and export the data and then create a new version of the database.  

I have no documentation to go by either :/

is there a restriction on the amount of joins you can do within a statement?
0
 
Terry WoodsIT GuruCommented:
I haven't come across any limitation on the number of joins, though there may well be one; it's probably too high to matter.

Keep in mind that comment I made earlier about left joins. You will miss some rows from your result if some students are, for example, missing the link to the tbl_Class table, which may or may not be what you want.
0
 
jay_eireAuthor Commented:
Thanks again for the help @TerryAtOpus.  

I will review the join documentation link you provided and try some variations of the joins for the other data exports.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now