Help with SELECT query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with SELECT query

Author

Topic

wales321
Starting Member

27 Posts

Posted - 2012-10-25 : 09:54:20

I was wondering if anyone could help me in constructing a SELECT query

I have a number of tables, modelling a university database.

In this query i want to select the ModuleID and Module_Name from my modules table of the module that are taught by a specific teacher.

The tables for this query are below.

Modules(ModuleID, Module_Name) Primary Key = ModuleID
Staff(StaffID, Staff_Name) Primary Key = StaffID
Teaches(StaffID, Staff_Name) Primary Key = StaffID, ModuleID Foreign Key = Staff(StaffID), Modules(ModuleID)

I am creating a java program which manages the database through a user interface. I want to enter the teachers name and it to return the modules taught by that teacher. I can't think of a way to get this into one select statement
I know i have to retrieve the StaffID using the Staff_Name
Then retrieve the ModuleID using the StaffID
And then retrieve the Module information using the ModuleID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 10:02:50

sounds a very simple join question. Can we see what you tried yet?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-25 : 10:17:55

I know i will be using the simple select query below to get get the module id and name from Modules
SELECT ModuleID, Module_Name FROM Modules WHERE
I cant get my head around how to find the staffID, compare that to StaffID in Teaches table and then compare the matching ModuleID with the Modules table.
Individually i could do each section i mentioned, just not sure how i would combine them into one, and where to use the join statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 10:25:17

quote:
Originally posted by wales321

I know i will be using the simple select query below to get get the module id and name from Modules
SELECT ModuleID, Module_Name FROM Modules WHERE
I cant get my head around how to find the staffID, compare that to StaffID in Teaches table and then compare the matching ModuleID with the Modules table.
Individually i could do each section i mentioned, just not sure how i would combine them into one, and where to use the join statement

look for join syntax here

http://www.w3schools.com/sql/sql_join.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-25 : 10:36:11

I have been looking on that websites, but that shows how to display columns form multiple tables, not how to get the result. Below i have added the separate queries i would need to use, but obviously the result from the second and third rely on the previous query. I am not sure how i would achieve this with join.

SELECT Staff.StaffID FROM Staff WHERE Staff_Name ='Joe Bloggs'
SELECT Teaches.ModuleID FORM Teaches WHERE ModuleID=''
SELECT ModuleID, Module_Name FROM Modules WHERE ModuleID = ''

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 11:12:44

its as simple as



SELECT m.Module_Name  ,s.Staff_Name 
FROM Staff s
INNER JOIN Teaches t
ON t.StaffID = s.StaffID
INNER JOIN Modules m
ON m.ModuleID = t.ModuleID 
WHERE s.Staff_Name ='Joe Bloggs'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-25 : 11:34:20

Brilliant! ive taken a while to understand how that statement works and it makes sense now.
I have implemented this into my program and it works perfectly.
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 11:35:20

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-25 : 11:52:49

One slight problem when i try to change it to display the module ID and Module name instead of Module Name and Staff Name
I have tried the following on got an error "MySQLSyntaxErrorException: Not unique table/alias: 'Modules'"


SELECT Modules.ModuleID, Modules.Module_Name FROM Modules INNER JOIN Teaches ON Teaches.StaffID = Staff.StaffID INNER JOIN Modules ON Modules.ModuleID = Teaches.ModuleID WHERE Staff.Staff_Name ='staff'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 12:24:05

you've same table specified twice it should be


SELECT Modules.ModuleID, Modules.Module_Name FROM ModulesStaff INNER JOIN Teaches ON Teaches.StaffID = Staff.StaffID INNER JOIN Modules ON Modules.ModuleID = Teaches.ModuleID WHERE Staff.Staff_Name ='staff'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-25 : 13:07:02

That works now, i am confused as to why we need the FROM Staff though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 15:00:27

quote:
Originally posted by wales321

That works now, i am confused as to why we need the FROM Staff though.

what?
how else could you filter on Staff_Name which is a column in Staff!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-25 : 15:35:09

Sorry, i didn't fully understand the query. I have now studied it and understand it fully. I have never used JOIN before so it was confusing at first. I have successfully implemented some more queries, slightly longer than this one, with no problem. Thanks for the help, and i now realise how stupid i looked :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 16:06:27

no problem

you're welcome

Glad that you understood the concept

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-26 : 13:51:44

To save starting another topic i thought i would add it here, seeing as it is also another SELECT Statement.
I have done a number of join queries now, the next one i have to do is print out staff who teach more than 1 module. Could someone advise me on the best way to do this? Would i be using a similar structure to the previous queries with join but the WHERE clause will search for multiple entries? How would i check whether there are multiple modules entries as week?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-26 : 15:02:29

quote:
Originally posted by wales321

To save starting another topic i thought i would add it here, seeing as it is also another SELECT Statement.
I have done a number of join queries now, the next one i have to do is print out staff who teach more than 1 module. Could someone advise me on the best way to do this? Would i be using a similar structure to the previous queries with join but the WHERE clause will search for multiple entries? How would i check whether there are multiple modules entries as week?

you would be using same join concept
but in this you need to create a derived table which identifies the multi module teaching staffs. You need to make use of GROUP BY for this and apply COUNT() or SUM()

so in your case it would be


SELECT s.Staff_Name
FROM Staff s
INNER JOIN (SELECT StaffID
            FROM Teaches 
            GROUP BY StaffID
            HAVING COUNT(DISTINCT ModuleID) > 1
           )t
ON t.StaffID = t.StaffID

the derived table
SELECT StaffID
FROM Teaches
GROUP BY StaffID
HAVING COUNT(DISTINCT ModuleID) > 1

will give you staff ids who deal with multiple Modules (see COUNT(DISTINCT..) usage)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-27 : 09:51:36

Thank you, i think i will have to try and tweak it slightly as currently i have output as

Staff 1
Staff 1
Staff 2
Staff 2
Staff 3
Staff 3

The first is correct but the second staff member teaches 3 modules and the third only teaches 1.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-27 : 21:14:46

quote:
Originally posted by wales321

Thank you, i think i will have to try and tweak it slightly as currently i have output as

Staff 1
Staff 1
Staff 2
Staff 2
Staff 3
Staff 3

The first is correct but the second staff member teaches 3 modules and the third only teaches 1.

nope...you wont get staff details unless they teach more than one module

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-28 : 12:53:20

I know there shouldn't be output unless the staff member teaches multiple modules but the output above is what i am currently getting.

I am using the statement below

SELECT Staff.Staff_Name FROM Staff INNER JOIN (SELECT StaffID FROM Teaches GROUP BY StaffID HAVING COUNT(DISTINCT ModuleID) > 1)Teaches ON Teaches.StaffID = Teaches.StaffID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 09:41:27

are there multiple records in teaches for same module and same staff?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wales321
Starting Member

27 Posts

Posted - 2012-10-29 : 10:26:22

The teaches table is the same as below.
Staff 1 Module1
Staff 2 Module2
Staff 3 Module3
Staff 1 Module3
Staff 2 Module4
Staff 2 Module1

So Staff 3 shouldn't appear, staff 1 should have two modules and staff 2 should have three modules.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources