Problem with multiple search keywords

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with multiple search keywords

Author

Topic

savvy
Starting Member

48 Posts

Posted - 2005-09-19 : 05:58:08

I am trying to do a search for multiple words in the database but i am getting the result in repeating order. This statement is doing search for each word and joining them but i want them not to repeat if the multiple words are in a single jobposition.

Declare @list varchar(200)
set @list = 'abap,hr,sap'

SELECT * FROM JobDetails AS J inner join dbo.Split1(@list, ',') AS S ON J.JobPosition LIKE '%' + S.Data + '%'

Result:
JobId JobPosition Location JobType Keywords
1 SAP ABAP consultant UK Contract abap
3 SAP HR ESS/MSS Consultant. Wales Contract hr
6 SAP HR Manager Middlesex Permanent hr
1 SAP ABAP consultant UK Contract sap
2 SAP Basis Consultant with SA UK Permanent sap
3 SAP HR ESS/MSS Consultant. Wales Contract sap
4 SAP MM Support Consultant London Contract sap
5 Managing Consultant SAP FI/C Germany Temporary sap
6 SAP HR Manager Middlesex Permanent sap

Thanks in Advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-19 : 06:02:40

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 06:05:34

SELECT DISTINCT J.*

maybe?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-19 : 06:10:06

savvy, you edited your topic
Can you post the result you get from your query and the result you want?

Madhivanan

Failing to plan is Planning to fail

savvy
Starting Member

48 Posts

Posted - 2005-09-19 : 06:12:22

cheers friends,
Distinct J.* is working perfectly as i want.
I am getting the result in the proper order without repeating.
The response was much faster than i expected.
Thank you very much

savvy
Starting Member

48 Posts

Posted - 2005-09-19 : 06:16:26

Declare @list varchar(200)
set @list = 'abap,hr,sap'

SELECT Distinct J.* FROM JobDetails AS J inner join dbo.Split1(@list, ',') AS S ON J.JobPosition LIKE '%' + S.Data + '%'

1 SAP ABAP consultant UK Contract
2 SAP Basis Consultant with SA UK Permanent
3 SAP HR ESS/MSS Consultant. Wales Contract
4 SAP MM Support Consultant London Contract
5 Managing Consultant SAP FI/C Germany Temporary
6 SAP HR Manager Middlesex Permanent

This was the result i was expecting which i got it
anyways thank you very much Madhavan for all ur help and interest

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 06:30:39

Performance will probably be faster if you do:


SELECT J.* 
FROM JobDetails AS J
     JOIN
     (
         SELECT DISTINCT J1.MyPK
         FROM JobDetails AS J1
             inner join dbo.Split1(@list, ',') AS S
                 ON J1.JobPosition LIKE '%' + S.Data + '%'
     ) J1
         ON J1.MyPK = J.MyPK

and if there are any TXT columns in "J.*" etc. I reckon that you will have to do it this way because you'll exceed the limits of "ORDER BY" used by the DISTINCT

Kristen

savvy
Starting Member

48 Posts

Posted - 2005-09-19 : 08:39:38

This is my final stored procedure which searches with respect to location, JobType, Region, list and Date.
They are all working individually but not all together
i know i've gone wrong somewhere but dont know where, can I perform a search with all the options together as well as individually
Thanks in Advance

CREATE PROCEDURE SelectSearch
(
@loc nvarchar(100),
@type nvarchar(50),
@region nvarchar(50),
@list nvarchar(200),
@a int
)
AS
SELECT DISTINCT J.* FROM JobDetails AS J JOIN
(
SELECT DISTINCT J1.JobID
FROM JobDetails AS J1
inner join dbo.Split1(@list, ' ') AS S
ON J1.JobPosition LIKE '%' + S.Data + '%'
) J1 ON J1.JobID = J.JobID
OR J.Location LIKE '%'+@loc+'%'
OR J.Location LIKE '%'+@region+'%'
OR J.JobType LIKE '%'+@type+'%'
OR J.JobDate BETWEEN DATEADD(dd,-@a-1,getdate()) AND GETDATE()
GO

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 13:28:31

Not sure I've fully captured your intention, but hopefully this will give you some ideas. It allows each "criteria" parameter to be passed as NULL (wildcard) or a specific value.

I've removed the DISTINCT on the outer SELECT as 1) it is inefficient and 2) it is better to allow it so that if you get multiple duplicate rows you know you have a bug! The inner sub-SELECT should take care of duplicates


CREATE PROCEDURE SelectSearch
(
	@loc nvarchar(100),
	@type nvarchar(50),
	@region nvarchar(50),
	@list nvarchar(200),
	@a int
)
AS
SELECT DISTINCT J.*
FROM	JobDetails AS J 
	JOIN
	(
		SELECT DISTINCT J1.JobID
		FROM	JobDetails AS J1
		inner join dbo.Split1(@list, ' ') AS S
			 ON J1.JobPosition LIKE '%' + S.Data + '%'
			AND (@loc IS NULL OR J1.Location LIKE '%'+@loc+'%')
			AND (@region IS NULL OR J1.Location LIKE '%'+@region+'%')
			AND (@type IS NULL OR J1.JobType LIKE '%'+@type+'%')
			AND (@a IS NULL 
				OR (J1.JobDate BETWEEN 
					DATEADD(dd,-@a-1,getdate()) 
					AND GETDATE()
				)
			)
	) J1 ON J1.JobID = J.JobID 
GO

Kristen

savvy
Starting Member

48 Posts

Posted - 2005-09-20 : 07:20:43

I have used the same idea n thats works great
Thanks for your replies and all ur help Kristen
cheers mate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources