Click here to Skip to main content
Click here to Skip to main content

Tagged as

How to Create a Relevance Based Search Query for SQL

, 31 Dec 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Arrange Results to get the most relevant result first...

Introduction

Many a times, we require to implement the Search algorithm in the applications. So in a normal case, we may search the term for various criteria and then find the best match for the Search Query. I also wanted a same thing where I wanted to search the Cars for my application. In my application, I wanted five criteria namely, Brakes, Fuel, Lock, Engine, and Steering. So the customer will select the options from the combo box (only valid values from the columns) and then display the Cars that match maximum features.

Background

For implementing this article, you must be aware of UNION, ORDER BY, GROUP BY operations of clauses and database connection in VB.NET.

Using the Code

We are going to learn two parts:

  1. How to load column in the Combo box so that user can select only valid values
  2. How to create a Search to get the best result

So starting off with that, I have a small snippet of my code. I just implemented the code as logic. We have five combo boxes from which user will select values and then by searching, the values will be loaded.

Dim connObj As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
	Application.StartupPath & "\Car_Showroom.mdb")
Dim commObj As New OleDbCommand
Dim myDataReader As OleDbDataReader
Dim myDataSet, myDataSet1 As New DataSet
Dim query(3) As String
Dim myAdapter As OleDbDataAdapter

These are the Database Connection objects. Here is the code to load a single Combo with that specific column.

  myDataSet1.Clear()
        connObj.Open()
        ''''''' Load Steering
        myDataSet1.Clear() ' clear all the values from Data Set if any
        cmbSteering.Items.Clear() ' clear all the items of the Combo BOx
        commObj = New OleDbCommand("SELECT DISTINCT Steering _
	FROM CarDetails", connObj) ' Create a command object and add connection
        myAdapter = New OleDbDataAdapter(commObj)
        myAdapter.Fill(myDataSet1, "cDetails") ' Execute the Query and 
					' fill the results in the dataset.
        cmbSteering.Text = "Select Company
' This is the most important code
For Each ds As DataRow In myDataSet1.Tables("cDetails").Rows 
            cmbSteering.Items.Add(ds("Steering").ToString())
        Next 

Explanation of the Above Code

We are creating a DataRow object and then using a For Each Loop we are adding the Steering Column value to the combo box.

This is the next part of the segment. This is what I searched on the internet but couldn't find one. So I worked upon and figured this out as one of the solutions.

Following shows the SQL Query and later its explanation:

 Dim steer As String = cmbSteering.SelectedItem
        Dim engine As String = cmbEngine.SelectedItem
        Dim fuel As String = cmbFuel.SelectedItem
        Dim lock As String = cmbLock.SelectedItem
        Dim brakes As String = cmbBrakes.SelectedItem
        myDataSet.Clear()
        connObj.Open()
        query(1) = "SELECT PRODID,Occurence FROM (SELECT ProdId,count(*) _
		AS Occurence FROM (SELECT ProdId FROM CarDetails WHERE _
		Steering = '{0}' UNION ALL SELECT ProdId FROM CarDetails _
		WHERE Engine ='{1}' UNION ALL SELECT ProdId FROM CarDetails _
		WHERE Fuel ='{2}' UNION ALL SELECT ProdId FROM CarDetails _
		WHERE Brakes ='{3}' UNION ALL SELECT ProdId FROM CarDetails _
		WHERE Lock ='{4}' ) Group By ProdID )ORDER BY Occurence DESC"
        query(2) = String.Format(query(1), cmbSteering.SelectedItem, _
		cmbEngine.SelectedItem, cmbFuel.SelectedItem, _
		cmbBrakes.SelectedItem, cmbLock.SelectedItem)
        commObj = New OleDbCommand(query(2), connObj)
        myAdapter = New OleDbDataAdapter(commObj)
        myAdapter.Fill(myDataSet1, "cDetails")
        connObj.Close() 

So now this is the Query stored in var=query(1).

Here, what we are doing is that first we'll search the terms individually in those columns. Like Steering in Steering, Brakes in Brakes, etc..

We'll then UNION all the results. UNION combines all the results in to single result set. But UNION does not add repeated results, and we need repeated results because the one that occurs most is the best match. Hence we are using UNION ALL.

So after the UNION, we now have the combined result. So now, we'll group all the results as per the number of occurrences. So we have a Super Query (and all the UNIONs as the sub query), that use COUNT(*) and group them by GROUP BY clause.

But again, this will sort the Result as the least matched to best matched. Hence we'll give COUNT(*) as Occurence and then sort the Occurence in Descending order using the ORDER BY clause.

So here, after implementing this query, I have returned the Product ID (Prod ID unique for all rows) and its occurrence. This is just a snippet, not a usable project. So you need to make changes as per your code.

I hope this post was helpful. Smile | :)

Points of Interest

This is my first ever article on CodeProject. I had been searching this so curiously, but couldn't find any relevant article or code for this. So if there are any errors, doubts, corrections, then please let me know. Excuse any mistake because I am not a professional. I am a student.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manan J
Web Developer
India India
Hey,
I am Manan Jadhav, working on Web Applications and Databases since 3 years. I started off to develop some applications, some basic ones and which then kick-started the freelancing projects from various companies and colleges. I have been always interested in doing things differently and as efficient as possible. My proficiency includes Web Development, Databases and .net.
 
My interests include programming and teaching.
 
I also have some research work going around on Artificial Intelligence and Databases.

Comments and Discussions

 
GeneralCouple of comments PinmemberTim Corey5-Jan-11 12:29 
Nicely done. That is a fairly efficient way to find the most relevant items in your search. I just have a couple comments that I thought might be useful to add to the discussion. The first one really doesn't pertain to the meat of this article but it might be useful for the future. When you populated your combo box, you looped through each row. You don't need to do that when you have a dataset available to you. You can simply need to use the following pattern:
 
'dst is our pre-loaded DataSet.  It has
'two fields: ID and Value.
ComboBox1.DataSource = dst.Tables("ComboList")
ComboBox1.DisplayMember = "Value"
ComboBox1.ValueMember = "ID"
 
The "Value" is the column name of the column that holds the data you want to display. The "ID" is the column name of the column you want to use as your row identifier.
 
When it comes to your actual query, it looks a bit confusing when it is written all together in code like that but it works well. I created a sample table in SQL to play with it a bit. I noticed that there were a few things that needed to be change to make is a direct SQL command. Beyond that, however, I did notice that you have one extra SELECT statement that you don't need. The outermost statement is just used to sort the records. You can put that sort on the next statement in without having an issue. Let me show you:
 
Here is your command converted over to T-SQL:
DECLARE @steering NVARCHAR(50)
DECLARE @engine NVARCHAR(50)
DECLARE @fuel NVARCHAR(50)
DECLARE @lock NVARCHAR(50)
DECLARE @brakes NVARCHAR(50)
 
SET @steering ='Power'
SET @engine = 'i-tec'
SET @fuel = 'Gas'
SET @Lock = 'Manual'
SET @brakes = 'Drum'
 
SELECT ProdID,Occurence FROM (
	SELECT ProdId,count(*) AS Occurence FROM (
		SELECT ProdId FROM CarDetails WHERE Steering = @steering 
		UNION ALL 
		SELECT ProdId FROM CarDetails WHERE Engine = @engine 
		UNION ALL 
		SELECT ProdId FROM CarDetails WHERE Fuel = @fuel 
		UNION ALL 
		SELECT ProdId FROM CarDetails WHERE Brakes = @brakes 
		UNION ALL 
		SELECT ProdId FROM CarDetails WHERE Lock = @lock 
	) x
	Group By ProdID 
) y 
ORDER BY Occurence DESC
 
Notice that I created variables to populate like you did in VB.NET only I did it in SQL since I'm not calling it from an application. Also, SQL wanted me to name the "tables" used in the FROM statements. I named them x and y.
 
Here is the same command with the one less SELECT statement:
DECLARE @steering NVARCHAR(50)
DECLARE @engine NVARCHAR(50)
DECLARE @fuel NVARCHAR(50)
DECLARE @lock NVARCHAR(50)
DECLARE @brakes NVARCHAR(50)
 
SET @steering ='Power'
SET @engine = 'i-tec'
SET @fuel = 'Gas'
SET @Lock = 'Manual'
SET @brakes = 'Drum'
 
SELECT ProdId,count(*) AS Occurence FROM (
	SELECT ProdId FROM CarDetails WHERE Steering = @steering 
	UNION ALL 
	SELECT ProdId FROM CarDetails WHERE Engine = @engine 
	UNION ALL 
	SELECT ProdId FROM CarDetails WHERE Fuel = @fuel 
	UNION ALL 
	SELECT ProdId FROM CarDetails WHERE Brakes = @brakes 
	UNION ALL 
	SELECT ProdId FROM CarDetails WHERE Lock = @lock 
) x
Group By ProdID 
ORDER BY Occurence DESC
 
Both commands end up with the same results. I'm not sure if Access has a limitation that SQL does not, so forgive me if this is only usable in SQL. The reason this works is because the ORDER BY command is the last to be evaluated (the commands are evaluated in the following order - FROM, WHERE, SELECT, ORDER BY).
 
In any event, nice job.
GeneralRe: Couple of comments PinmemberManan J9-Jan-11 3:08 
GeneralRe: Couple of comments PinmemberTim Corey10-Jan-11 4:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411028.1 | Last Updated 31 Dec 2010
Article Copyright 2010 by Manan J
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid