• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Logic problem

 
Ranch Hand
Posts: 2676
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is more of a logic problem or perhaps an sql problem than a java problem. I am trying to search a database based on information entered by the user. They have the option of entering nothing, in which case all records are displayed. The possible inputs come from two different tables. The possible outputs come from 3 different tables that are being tied together with information from another two tables(one of which is used in the input). Does anyone have a hint for me on the logic of this process?
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Any chance you'd be willing to sketch out the structure of the tables? the fields of interest? the inputs and outputs you're talking about?
(Or fictitious but similarly structured tables if you can't talk about the real thing.)
Are you looking to write a single SQL query or multiple queries? If multiple queries, does some external program choose between?
 
Matthew Phillips
Ranch Hand
Posts: 2676
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The table structure is as follows:
Tables:


USER
***userid
firstName
lastName
email
homePhone
userType
password


SKILLS
***skillid
skill



ALUMNI-SKILL
***skillid
***alumniid



ALUMNI
***alumniid
userid
street
city
state
zip
GPA
major


EMPLOYER
***employerid
employerName
employerPhone
employerStreet
employerCity
employerState
employerZip



ALUMNI-EMPLOYER
***employerid
***alumniid
jobTitle
dateStarted
jobDuties


ALUMNI-CERTIFICATION
***alumniid
***certificationID
certificationDate


CERTIFICATION
****certificationID
certification
The output fields are employerName, employerStreet, employerCity, employerState, employerZip, employerPhone, jobDuties, and skills.
The input options are city, state, and skills. If any of the fields are blank, the system should assume that the user does not care about it and not search on it. All employers are tied to an alumni, so if the alumni doesn't exist then the employer doesn't exist. This is completely baffling me.
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here's a first shot at the maximum query that will return all results. Are you at least comfortable with the way this query connects the tables?
All joins here are inner (though that may change later). Couple fields are left out. I kept all the keys in the select list even though they're not necessary. It's just helpful to see them at this state.
Again, are you trying to write a single query or multiple queries to handle this?

Also, what product are you using? Do you prefer '=' for inner joins or INNER JOIN syntax?
When you say
If any of the fields are blank, the system should assume that the user does not care about it and not search on it.
Does that mean not qualify on it? In other words, if "state" is left blank, should the system return employers in all states?
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Matthew Phillips:
All employers are tied to an alumni, so if the alumni doesn't exist then the employer doesn't exist. This is completely baffling me.[/QB]


Does the statement I italicized apply to query results or the structure of the underlying data?
If an alumni changes employers should my the employer be deleted? Or should the entry on ALUMNI-EMPLOYER just be updated to reflect the new employer.
Should you be able to associate an employer with multiple alumni?
[ May 21, 2002: Message edited by: Michael Matola ]
 
Matthew Phillips
Ranch Hand
Posts: 2676
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your example on states in correct. If a state is not entered, then employers in all states are returned. I think that I would prefer seperate sql statements because I am not completely comfortable with joins yet. I think I can figure out the seperate sql statements if I can figure out how to structure my logic. Pulling the output all together is where I am losing it.
 
Matthew Phillips
Ranch Hand
Posts: 2676
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ALUMNI_EMPLOYER is updated is a person changes jobs. Deleting employers is a seperate function. If there is no alumni working for an employer then the employer should not be returned as a part of this search.
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I played with this some and it looks like you can cover all cases with a single query.
I simplified the original a bit by dropping the alumni table altogether and joining alumni_skill and alumni_employer directly:

The last three conditions
and als.skillId = 'JAVA'
and e.employerCity = 'Faultville'
and e.employerState = 'CA'
are just placeholders for code that should be generated on the fly based on which (if any) criteria the user selected. For example, if the user doesn't enter a skill, don't generate an "als.skillId =" condition. (Note: you can change any of the equals to IN if you're allowing multiple selections per field.)
Is this starting to make more sense or are there points you're still confused on?
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If there is no alumni working for an employer then the employer should not be returned as a part of this search.
Ok, I was just checking to make sure there weren't some more complicated things going on.
Your requirement of returning only employers that have alumni working for them is met by having the employer table inner join to a table on which alumniId is a key (in our case, it's alunni_emplid).
 
Matthew Phillips
Ranch Hand
Posts: 2676
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think it makes sense, but I will know for sure after I play with it. Thanks for your help.
 
Matthew Phillips
Ranch Hand
Posts: 2676
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is my translation of the SQL statement into English. Does this sound right? When the skillID of skills equals the skillID in alumni_skill and the alumnID alumni_skill = the alumniID of alumni_employer and the employerID of alumni_employer = the employerID of employer and the skillID of alumni skill = the skill id input from the user and the employerCity of employer = the city input by the user and the employer state of employer = the state input by the user, then the output will be the selected skill names from the skill table, the jobDuties from alumni_employer, the employerName from employer, the employerCity from employer, and the employerState from employer.
 
reply
    Bookmark Topic Watch Topic
  • New Topic