Solved

Select Statement from mutliple tables

Posted on 2013-11-19
13
326 Views
Last Modified: 2013-11-19
Hi,
I am trying select all the class names from tbl_class_Names that are not listed in
tbl_students.

I have tried this select statement however it does not work..

select tbl_Class_Names.Class_Name 
from tbl_Class_Names
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
where tbl_Students.Class_ID <> tbl_Class on tbl_Class.Class_ID

Open in new window


I have inherited the database so the structure is not ideal.

tbl_Students
Student_Number            Class_ID                  Lecture_Room
511                              18                             5
512                              15                             9


tbl_Class

Class_ID            CN_ID
18            55            
15            76


tbl_Class_Names
CN_ID            Class_Name
55               ScienceY2      
76               MathY3
0
Comment
Question by:jay_eire
  • 6
  • 5
  • 2
13 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39660208
This should work:

select tbl_Class.Class_ID, tbl_Class.CN_ID, tbl_Class_Names.Class_Name
from tbl_Class
JOIN tbl_Class_Names on tbl_Class.CN_ID = tbl_Class_Names.CN_ID
where tblStudents.Class_ID not in (select tbl_Class.Class_ID from tbl_Class)

Open in new window

0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39660224
select cn.class_name
from tbl_class_names cn
   INNER JOIN class c ON cn.cn_id = c.class_id
WHERE c.class_id not in (select class_id from tbl_students)

Open in new window

0
 
LVL 7

Author Comment

by:jay_eire
ID: 39660308
thanks for reply

@sjwales
I am getting this error message back
Error Code: 1054. Unknown column 'tbl_Students.Class_ID' in 'IN/ALL/ANY subquery'      0.000 sec
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 39660328
corrected join condition :

select cn.class_name
from tbl_class_names cn
   INNER JOIN class c ON cn.cn_id = c.cn_id
WHERE c.class_id not in (select class_id from tbl_students)

Open in new window

0
 
LVL 7

Author Comment

by:jay_eire
ID: 39660366
@ jacko72 thanks what is c and cn? are these temporary values?
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39660372
They are aliases, you use them as a shortcut for the table names
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 7

Author Comment

by:jay_eire
ID: 39660399
thanks jack072 I have an error

Error Code: 1052. Column 'cn_id' in field list is ambiguous      0.000 sec
0
 
LVL 7

Author Comment

by:jay_eire
ID: 39660405
my bad, I have corrected it had  WHERE c.cn_id instead of WHERE c.class_id
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39660409
ensure you have prefixed all the column names with an alias or table name.
0
 
LVL 7

Author Comment

by:jay_eire
ID: 39660470
if I need to select additional columns from tbl_Class_Names how do I do this with the alias?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39660479
My query was incorrect, reversed the conditions, Jacko's looks more like the trick.

Tbl_Class_Names is aliased above as "cn".

You can add extra columns by cn.col1, cn.col2.  Just because a table is aliased, by the way, doesn't mean you still can't reference it by its full name.

So you could do cn.col1 or you could do tbl_Class_names.col1
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39660483
The alias for tbl_class_names is cn, you can see the alias after the tablename in the FROM clause : from tbl_class_names cn
You just need to prefix any colum name with the alias then a full stop and then the column name like : cn.class_id

so:

select cn.class_id, cn.class_name
from tbl_class_names cn
   INNER JOIN class c ON cn.cn_id = c.cn_id
WHERE c.class_id not in (select class_id from tbl_students) 

Open in new window

0
 
LVL 7

Author Comment

by:jay_eire
ID: 39660589
thank you kindly for the help.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Have issues with Query MySQL 9 55
Alter an update query which rounds 7 33
Amazon Redshift 2 28
What query can i write to find where a function is 4 23
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…
Creating and Managing Databases with phpMyAdmin in cPanel.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

862 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

27 Experts available now in Live!

Get 1:1 Help Now