Solved

SQL Join Statement

Posted on 2013-11-17
11
404 Views
Last Modified: 2013-11-17
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
Comment
Question by:jay_eire
  • 7
  • 4
11 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39655055
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39655062
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
 
LVL 7

Author Comment

by:jay_eire
ID: 39655072
thanks for reply TerryAtOpus, its not a homework assignment I'm afraid, it is a genuine query.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39655073
There's documentation on joins here: http://dev.mysql.com/doc/refman/5.0/en/join.html
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39655078
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 35

Expert Comment

by:Terry Woods
ID: 39655082
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
 
LVL 7

Author Comment

by:jay_eire
ID: 39655087
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
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 39655093
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
 
LVL 7

Author Comment

by:jay_eire
ID: 39655109
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39655154
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
 
LVL 7

Author Comment

by:jay_eire
ID: 39655162
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now