File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql SELECT problem

 
tom davies
Ranch Hand
Posts: 168
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Pie
Posts: 20161
24
MySQL Database
  • 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Really???

 
William P O'Sullivan
Ranch Hand
Posts: 859
Chrome IBM DB2 Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try GROUPing and HAVING clauses ;)

WP
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic