Search In Fulltextindexes For Multiple Searchterms In Multiple Columns
Mar 23, 2007
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
Does anyone know a better solution?
Thanks in advance Bart Rouw
View 2 Replies
ADVERTISEMENT
May 16, 2008
Hi
I have a table with 9 fields that I need to check. I want to check if "text1" and "text2" is in any of thoose 9 fields. But I dont want to check the combination of "text1" and "text2" in the same column. I want for example be able to check if "text1" is in column 1 to 9 and if "text2" is in column 1 to 9. Can someone give me a suggestion on how to do this?
View 6 Replies
View Related
May 30, 2006
Is it possible to put a set of columns into a select statement using "in"
For example:
select count(*) from table1 where (col1, col2, col3, col4) in (select a.col1, a.col2, a.col3, a.col4 from table1 a where........)
This is possible in this form for Oracle and DB2 but I cannot get it to work in Sql Server.
Any ideas?
View 7 Replies
View Related
Feb 10, 2004
I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)
I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.
I'm lost on both the search and the results code.
I'd like to have a drop down for the search like this:
<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>
Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>
Any ideas on how to make this work would be greatly appreciated.
View 3 Replies
View Related
Jul 20, 2005
********************alt.php.sql,compdatabases.ms-sqlservermicrosoft.public.sqlserver.programming***********************************Why doesn't this work:SELECT *FROM 'Events'WHERE dayofweekREGEXP 'monday' OR description REGEXP 'monday'When this does work:SELECT *FROM `Events`WHERE dayofweekREGEXP 'monday'
View 6 Replies
View Related
Sep 19, 2007
Hi,
I have tried this code from http://jtkane.spaces.live.com/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry for full-text search on multiple tables & columns.
Here's my code:
SELECT * from [tStaffDir] AS e, [tStaffDir_PrevEmp] t,CONTAINSTABLE([tStaffDir], *, @Name) as AwhereA.[KEY] = e.[ID] andt.[ID] = e.[ID]
I have FT the both the tables above and I am able to get results from the [tStaffDir] table but not the [tStaffDir_PrevEmp] table.The [tStaffDir_PrevEmp] table does have a column (which is [ID]) that is indexed, unique and non-Nullable.Please advise what I should do and look out for.
Many Thanks.
View 2 Replies
View Related
Jul 20, 2015
What is the most efficient way to write an SP to tackle all kinds of combinations here (where a user could give any search input).I know this must be fairly common to come across this situation.I have written an SP which will take in all the parameters and based on "IF" statements and using "LIKE" in SQL, this SP returns search results.But I wanted to know if there was more efficient ways of doing this, as you can imagine you might end up having several combinations of IF conditions.
View 9 Replies
View Related
Jun 23, 2015
I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...
In this case the best solution is to create an index on each column individually or at least the most used?
View 7 Replies
View Related
Dec 21, 2007
Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
Thanks in advanced
View 4 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
Apr 23, 2008
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
View 3 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Mar 2, 2015
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
View 3 Replies
View Related
Feb 12, 2015
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I need results for each parent like this
ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]
View 1 Replies
View Related
Sep 26, 2007
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
View 1 Replies
View Related
Apr 2, 2008
Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.
View 2 Replies
View Related
Sep 7, 2007
Hi,
I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.
Heres what i want to do:
This is the how the table is:
EmpID Designation
678
CFA
679
CFA
680
CFA
685
CFP
685
CIMA
685
IMCA
I want it to display as:
EmpID Designation1 Designation2 Designation3
678 CFA
679 CFA
680 CFA
685 CFP CIMA IMCA
could anyone provide some help on this?
Thanks
View 1 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
Jan 31, 2005
I'm heaving a heck of a time to get a sql query working. I'm being passed a series of zip codes and I need to return a list of all the rows that match those zip codes in a database of about 40,000 leads.
Can someone clue me in to what the commandtext of my query should look like?
Thanks!
View 4 Replies
View Related
May 7, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
View 6 Replies
View Related
Mar 2, 2007
I've done some simple sql's for searching a field using Like,But this one is different.
I am adding a param named @searchText
I would like to bring back all records in all the fields listedbelow that has that string in the field...
WHERE a.manufacturer = b.manufacturerIDAND a.location = c.locationIDAND a.Status = d.statusIDAND a.EquipmentType = e.IDAND a.calLab = f.ID AND a.testTechnology = g.id AND (c.locationID = @location OR @location = 0)
So, each line/field above I want to search for the string and includein the dataset.
Anyone can point me in the right direction?
Thanks,
Zath
View 7 Replies
View Related
Sep 23, 2005
I have a search page, containing 3 drop down lists. Theses are used to match data in 3 seperate collumns of a table.I can get the search to work when all boxes are completed, but I need to be able to leave some blank.A similar question was asked in http://forums.asp.net/626941/ShowPost.aspxAnd a very good solution was give: http://www.sqlteam.com/item.asp?ItemID=2077But I don't seem to be able to get any of these examples working!If I use the SET @SQL method, I get an error message saying that my input parameter has not been declared, when it has.When I use the COALESCE method, there is no data returned.(I am passing the data drop down list data fin the search page through the query string to a results page).Does anyone know of any other examples, or sample coding??Thanks...
View 2 Replies
View Related
Feb 14, 2006
Hi,
I want to search through 3 tables (TableB, TableC, TableD) to find
which hdr_ctl_nbr(s) are on those tables but not on TableA. In other words, TableA should match the combined tables B, C, and D but it doesn't, so I want to find what's missing on TableA. I know how to search TableB (see SELECT below) but how would I add TableC and TableD to this statement ? Thanks, Jeff
Select hdr_ctl_nbr, count(*) from TableA c
where c.hdr_ctl_nbr not exists (select hdr_ctl_nbr from TableB)
group by c.hdr_ctl_nbr
View 7 Replies
View Related
Aug 12, 2014
MS SQL 2008 R2
I have the following effectively random numbers in a table:
n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i
I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.
if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1
So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.
declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers
View 5 Replies
View Related
Sep 15, 2007
Hi,
I want to search multiple words that is present in the database, e.g if i am putting "porperty in south delhi" but south word is in the data base, but result does not comes. if I use like operator like this select * from MASTERSEARCH where companyname like '" + txt_Company.Text + "%' or dealsin like '%" + txt_keywords + "%'";Here I put only south word, then result comes. but I want search criteria should be any word that is present in the database.I am using this with my web site. http://www.b2bindialinks.com
View 1 Replies
View Related
Mar 11, 2008
I using multiple keywords to search and filter my data by using Stored Proc and retrieve the results in GridViewStored Proc ========ALTER PROCEDURE dbo.search
(
@eName nvarchar(30), @ID nvarchar(7), @cID nvarchar(10), @eCC nvarchar(3)
)
AS
SELECT eName, ID, cID, eCC
FROM iTrns
WHERE (ID LIKE '%' + @ID + '%') and
(eName LIKE '%' + @eName + '%') and
(cID LIKE '%' + @cID + '%') and
(eCC = @eCC)
========
My code behind========protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource = GetTable();
GridView1.DataBind();
}public DataSet GetTable()
{SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["***"].ConnectionString);
SqlDataAdapter Adptr = new SqlDataAdapter();SqlCommand cmd = new SqlCommand("search", SqlCon);
cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@ID", TextBox2.Text);
cmd.Parameters.AddWithValue("@eName", TextBox1.Text);cmd.Parameters.AddWithValue("@cID", TextBox3.Text);
cmd.Parameters.AddWithValue("@eCC", TextBox4.Text);
Adptr.SelectCommand = cmd;
DataSet ds = new DataSet();return ds;
SqlCon.Close();
}
========
Sometimes it retrieves the whole data !! and sometimes retrieve nothing !!
Any hint ??
Thank you
View 21 Replies
View Related
Sep 20, 2005
I am building job search engine. I got one listbox and one dropdownlist of place and region. If I select East Anglia from places the dropdownlist of regions it will display Any region, Cambridgeshire, Norfolk, Suffolk and if I Select Greater London it will display Any region, London, London-Central, London-East etc.. etc..I am searching for jobs with respect to regions, Is it possible that if i select say East Anglia in the listbox and Any region in the dropdownlist and i can search with all the regions in that section selected.At the moment i'm using this stored procedure which doesn't include the above idea. Is there any way to modify this Thanks in AdvanceSELECT DISTINCT J.*FROM JobDetails AS J JOIN(SELECT DISTINCT J1.JobIDFROM JobDetails AS J1inner join dbo.Split1(@list, ' ') AS SON J1.JobPosition LIKE '%' + S.Data + '%'AND (@loc='0' OR J1.Location LIKE '%'+@loc+'%')AND (@region='0' OR J1.Location LIKE '%'+@region+'%')AND (@type='0' OR J1.JobType=@type)AND (@a IS NULL OR (J1.JobDate BETWEEN DATEADD(dd,-@a-1,getdate()) AND GETDATE()))) J1 ON J1.JobID = J.JobIDsavvy
View 2 Replies
View Related
Mar 20, 2012
I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.
I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.
View 5 Replies
View Related
