my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes Advanced SQL Select Statement. Please Help! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Advanced SQL Select Statement. Please Help!" Watch "Advanced SQL Select Statement. Please Help!" New topic

Advanced SQL Select Statement. Please Help!

Cloey Tan
Ranch Hand

Joined: Feb 03, 2009
Posts: 60
I need some help in constructing an advanced sql select statement for report generation. This is my case:
I need to produce a report for workers without wages for a period of time from certain department.
My print report screen would have 2 dropdown fields - period and department. In order to identify those workers without wages, i need to search from a few tables, for example, harvesting , jobcard, transport to make sure that the worker name/id doesn't appear inside the record and the total working hour is 0 if his record exist within the selected date.

This seems to involve lots of selection, where shouldi start from. First select all the active users from the vallid department, then only filter based on whether his record exist in those other tables. Please advice. Thanks in advance.
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Its pretty much impossible to write SQL without knowing the data model, and we don't know the data model! Can you post a link to an ER diagram or something?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Cloey Tan
Ranch Hand

Joined: Feb 03, 2009
Posts: 60
Hi there Paul, thanks for your reply. It was not easy to generate the diagram, and ive reverse generated one sample - removing lots of other details. Hope it helps a bit. Ive attached together with this post.
Hrmstaff is the workers table, while the rest are tables which would be used to identify the working hours of staff. For example, each of those tables (other than hrmstaff) have fields like 'worker' (pfmjobcard), harvestName (pfmharvestdetail)..etc

Note: Having some problem attaching the file at the moment
I agree. Here's the link:
subject: Advanced SQL Select Statement. Please Help!
It's not a secret anymore!