File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes sql SELECT problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "sql SELECT problem" Watch "sql SELECT problem" New topic

sql SELECT problem

tom davies
Ranch Hand

Joined: Apr 27, 2012
Posts: 168
I have a java program which connects to a MySQL server and performs simple tasks like adding, removing and updating entries. I am also creating some reports but i am stuck on 1 select query which isnt giving me the desired output.
The tables needed for this query are:
Staff: StaffID, Staff_Name

Modules: ModuleID, Module_Name, Credits

Teaches: StaffID, ModuleID

I am trying to select the staff members who teach more than one module, so far i have this

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

But this is giving me output of

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

Where staff 1 teaches two modules, staff 2 teaches three modules and Staff 3 only teaches one module
Paul Clapham

Joined: Oct 14, 2005
Posts: 19869


William P O'Sullivan
Ranch Hand

Joined: Mar 28, 2012
Posts: 859

Try GROUPing and HAVING clauses ;)

subject: sql SELECT problem
It's not a secret anymore!