Solved

SQL Join Statement

Posted on 2013-11-17
11
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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
 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot table 2 52
sql update 2 45
Return Rows as per Quantity of Columns Value In SQL 6 34
Data conversion error in SSIS 3 52
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 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