Solved

Help required implementing a search clause algorithm - SQL & Java

Posted on 2013-01-31
4
326 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
  • 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now