• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Select Statement from mutliple tables

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
jay_eire
Asked:
jay_eire
  • 6
  • 5
  • 2
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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
 
Paul JacksonSoftware EngineerCommented:
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
 
jay_eireAuthor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Paul JacksonSoftware EngineerCommented:
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
 
jay_eireAuthor Commented:
@ jacko72 thanks what is c and cn? are these temporary values?
0
 
Paul JacksonSoftware EngineerCommented:
They are aliases, you use them as a shortcut for the table names
0
 
jay_eireAuthor Commented:
thanks jack072 I have an error

Error Code: 1052. Column 'cn_id' in field list is ambiguous      0.000 sec
0
 
jay_eireAuthor Commented:
my bad, I have corrected it had  WHERE c.cn_id instead of WHERE c.class_id
0
 
Paul JacksonSoftware EngineerCommented:
ensure you have prefixed all the column names with an alias or table name.
0
 
jay_eireAuthor Commented:
if I need to select additional columns from tbl_Class_Names how do I do this with the alias?
0
 
Steve WalesSenior Database AdministratorCommented:
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
 
Paul JacksonSoftware EngineerCommented:
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
 
jay_eireAuthor Commented:
thank you kindly for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now