Solved

SQL Join Statement

Posted on 2013-11-17
11
406 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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