Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

A SQL Problem: Help Please

 
Mark Lau
Ranch Hand
Posts: 120
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In my database, there is a Car table, which contains the following columns:
CarID CarMake CarModel CarYear CarMileage CarColor
I want to do a web application where users can search the Car table by entering a keyword such as "Ford".
Usually, we do
SELECT * FROM CAR WHERE CarMake='Ford'
But the problem here is that the user may well enter "Taurus", in which case the SQL query statement should be
SELECT * FROM CAR WHERE CarModel='Taurus'
And he could even enter "1997", in which case the SQL query statement should be
SELECT * FROM CAR WHERE CarYear='1997'
So, how can I have only one SQL query statement to achieve my goal? Thanks a lot.
Gene.
 
Craig Demyanovich
Ranch Hand
Posts: 173
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I havne't written any web applications in my work, but I don't think such experience is a requirement to answer your question.
Why not provide three different keyword fields on your web page, one for each criteria by which a user can search the table? You could choose to allow a search based on only one of these fields (your question indicates that this type of search is what you've considered), or you could allow the user to complete all three fields to narrow the search. Choose the one that best meets your current needs, which, I assume, is the first solution.
Have three SQL queries, each one executed from its own method: findByMake(String make), findByModel(String model) and findByYear(int year). Based on the field that contains data, execute the appropriate method. Note that you would want to check for appropriate values for the year, i.e., no negative numbers or numbers greater than the current year + 1, for example. Finally, once you have a working solution, you could refactor the common logic into a method that the above find methods use.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic