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...


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.


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.

        ''''''' 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 

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
        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")

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.


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


About the Author

Manan J
Web Developer
India India
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

GeneralMy vote of 5 PinmemberBilal Fazlani31-Aug-12 3:56 
GeneralCouple of comments PinmemberTim Corey5-Jan-11 12:29 
GeneralRe: Couple of comments PinmemberManan J9-Jan-11 3:08 
Hello Sir,
I read your comment and Yes all of your implementations are correct. I tried the same thing for that Combo Box Drop Down List but it didn't seem to work properly. May be there would be some other error because when I tried your code it worked fine.
And about SQL, I didn't see only that the ProdId Column is returned along with the Column Occurrence. Thank you for pointing it and it works fine with Access also.
This was my first article. I ll try and improve the next ones and would wait for your reply so that I can make it better. Thank You for replying, Smile | :)
Regards Manan J.

GeneralRe: Couple of comments PinmemberTim Corey10-Jan-11 4:00 
GeneralMy vote of 5 PinmemberManan J1-Jan-11 21:38 
GeneralRe: My vote of 5 PinmemberVijay Gill27-Jan-11 23:40 
GeneralRe: My vote of 5 PinmemberManan J31-Jan-11 17:58 

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.150327.1 | Last Updated 31 Dec 2010
Article Copyright 2010 by Manan J
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid