Solved

Help required implementing a search clause algorithm - SQL & Java

Posted on 2013-01-31
4
336 Views
Last Modified: 2013-04-23
I need to perform search on a table by implementing an algorithm(either in the front end java code or in the back end sql query I write). For your reference, I have pasted the sql code for the table I am using.

CREATE TABLE "tbl_reference_databases" (
 "id" integer primary key autoincrement,
 "created" datetime DEFAULT NULL,
 "modified" datetime DEFAULT NULL,
 "refdata_content_code" varchar(10) DEFAULT NULL,
 "refdata_profession_passion_code" varchar(10) DEFAULT NULL,
 "refdata_attribute_code" varchar(8) DEFAULT NULL,
 "refdata_sex_code" varchar(1) DEFAULT NULL,
 "refdata_age_code" varchar(2) DEFAULT NULL,
 "refdata_location_code" varchar(4) DEFAULT NULL,
 "refdata_language_code" varchar(4) DEFAULT NULL,
 "refdata_foodhabit_code" varchar(1) DEFAULT NULL,
 "refdata_drinkhabit_code" varchar(1) DEFAULT NULL )

Open in new window


From this table, I need to get the value corresponding to 'refdata_content_code' . The fields that would be included in WHERE clause is :

 
"refdata_drinkhabit_code"
"refdata_foodhabit_code"
"refdata_language_code"
"refdata_location_code"
"refdata_age_code"
"refdata_sex_code"
"refdata_attribute_code"
"refdata_profession_passion_code"

The requirement with this search is, I need to have an algorithm such that, I could prioritize the results based on the number of fields included in the WHERE clause. If the WHERE clause involves all the 8 fields, then the result I get for 'refdata_content_code' could be prioritized as 1, and after that remove fields one by one in the WHERE clause.

Finally, if I get a result with a single field in the WHERE clause, that is the last priority result.


So, the first WHERE clause would include all the fields given above and  after that, fields would be removed one by one in the ascending order of the fields given above.


I am not sure, whether this could be achieved with a single query or multiple queries. I do not even know what is the best approach I should use.

The front-end code I use is java.

Any help from the EE experts who could help me implement this algorithm would be much appreciated with points, surely.

If I need to clarify any portion of my question, kindly let me know.
0
Comment
Question by:Sreejith22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 38842169
you can try something like this in SQL
Select refdata_content_code, Flag1 + Flag2 + Flag3 + Flag4 + Flag5 + Flag6 + Flag7 + Flag8 [Rank]
From ( Select  refdata_content_code,
case when refdata_drinkhabit_code = @refdata_drinkhabit_code then 1 else 0 end Flag1,
case when refdata_foodhabit_code = @refdata_foodhabit_code then 1 else 0 end Flag2,
case when refdata_language_code = @refdata_language_code  then 1 else 0 end Flag3,
case when refdata_location_code = @refdata_location_code then 1 else 0 end Flag4,
case when refdata_age_code = @refdata_age_code then 1 else 0 end Flag5,
case when refdata_sex_code = @refdata_sex_code then 1 else 0 end Flag6,
case when refdata_attribute_code = @refdata_attribute_code then 1 else 0 end Flag7,
case when refdata_profession_passion_code = @refdata_profession_passion_code then 1 else 0 end Flag8

from tbl_reference_databases Where 
refdata_drinkhabit_code = @refdata_drinkhabit_code
or refdata_foodhabit_code = @refdata_foodhabit_code
or refdata_language_code = @refdata_language_code 
or refdata_location_code = @refdata_location_code
or refdata_age_code = @refdata_age_code
or refdata_sex_code = @refdata_sex_code
or refdata_attribute_code = @refdata_attribute_code
or refdata_profession_passion_code = @refdata_profession_passion_code) as A
order by [Rank] desc

Open in new window

0
 

Author Comment

by:Sreejith22
ID: 38842411
will this work with sqlite database?
0
 
LVL 39

Expert Comment

by:appari
ID: 38842428
not familiar with SQLite syntax. just give it a try and see.
0
 
LVL 39

Expert Comment

by:appari
ID: 38842430
syntax i posted is of SQLServer. replace all @... with corresponding search parameter values.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question