Solved

Help required implementing a search clause algorithm - SQL & Java

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
email the result out from a T-SQL queries 29 62
SQL Login 17 37
Sql query 107 22
SQL - How to list all tables participating in a query 7 37
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Viewers will learn how the fundamental information of how to create a table.

930 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