• 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
  • Ron McLeod
  • Paul Clapham
  • Devaka Cooray
  • Tim Cooke
Sheriffs:
  • Rob Spoor
  • Liutauras Vilda
  • paul wheaton
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Mikalai Zaikin
  • Carey Brown
  • Piet Souris
Bartenders:
  • Stephan van Hulst

A SQL Problem: Help Please

 
Ranch Hand
Posts: 120
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 173
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Don't destroy the earth! That's where I keep all my stuff! Including this tiny ad:
Gift giving made easy with the permaculture playing cards
https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing
reply
    Bookmark Topic Watch Topic
  • New Topic