• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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