search query - SQL Server Forums

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)

search query

Author

Topic

samham
Starting Member

12 Posts

Posted - 2003-04-28 : 12:43:22

I have 3 tables

Para(paraID,paraTitle,paraDate)
Country(countryID,countryName)
paraCountry(paraID,countryID)

a paragraph can be related to many countries
i want the user to be able to get the paragraph based on the countries he chooses

i have 2 possibilities:
the user can choose to have ALL the countries he selects included for a paragraph or ANY country.

the ANY possibility is done like this:

SELECT paraID, paraTitle
FROM para a
WHERE EXISTS
(
SELECT NULL
FROM paraCountry c
where a.paraID = c.paraID
and countryID in (1,2,3)
)

As for the ALL possibility i want that all the countries he chooses included in a paragraph.. so if a paragraph is related to countryID 1,2,3,4,5 and the user chooses 1,3,5 the paragraph is selected

thx

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-28 : 13:46:52

SELECT DISTINCT ParaID,ParaTitle FROM Para A INNER JOIN ParaCountry PC ON A.ParaID = PC.ParaID WHERE PC.CountryID IN(Your Choice1, Your Choice2,etc.)
OR:
SELECT ParaID,ParaTitle FROM Para A INNER JOIN ParaCountry PC ON A.ParaID = PC.ParaID WHERE PC.CountryID IN(Your Choice1, Your Choice2,etc.) GROUP BY A.ParaID,A.ParaTitle

Sarah Berger MCSD

samham
Starting Member

12 Posts

Posted - 2003-04-28 : 14:32:19

ur query is identical to mine.. this is my first possibility, the ANY possibility.
this query will return results if ANY of the choices is found ...

what i want is the other possibility which returns results only if ALL the possibilities match

so if i want choice1, choice2 i only get results where paragraphs are related to choice1, choice2 in the same time , not if choice1 OR choice2 r present

so the idea is to get the results if the user choices of countries(many choices) are INCLUDED in the paragraphs choices in the paraCountry table

hope this makes it more clear

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-28 : 15:06:21

How About:


CREATE TABLE Para(paraID int,paraTitle varchar(255),paraDate datetime) 
GO
CREATE TABLE Country(countryID int,countryName varchar(50)) 
GO
CREATE TABLE paraCountry(paraID int,countryID int)
GO
INSERT INTO Para (ParaId, ParaTitle, ParaDate) SELECT 1, 'This Article', Getdate()
GO
INSERT INTO Country(countryId, countryName) SELECT 1, 'USA' UNION ALL SELECT 2, 'GB' UNION ALL SELECT 2, 'Canada'
GO
INSERT INTO paraCountry (ParaId, CountryID) SELECT 1,1 UNION ALL SELECT 1,2 UNION ALL SELECT 1,3
GO
DECLARE @UserSelect1 Int, @UserSelect2 Int, @UserSelect3 Int 

SELECT @UserSelect1 = 1, @UserSelect2 = 2, @UserSelect3 = 3

CREATE TABLE #Temp(UserSelect Int)

INSERT INTO #Temp(UserSelect) SELECT @UserSelect1 UNION ALL SELECT @UserSelect2 UNION ALL SELECT  @UserSelect3

SELECT  Distinct ParaId o
  FROM ParaCountry 
 WHERE EXISTS (SELECT 1 FROM #Temp t LEFT JOIN paraCountry i ON t.UserSelect = i.CountryId
		WHERE o.ParaId = i.ParaId AND i.CountryId Is Not Null HAVING Count(*) = (SELECT Count(*) FROM #Temp))

DELETE FROM ParaCountry WHERE CountryID = 3

SELECT  Distinct ParaId o
  FROM ParaCountry 
 WHERE EXISTS (SELECT 1 FROM #Temp t LEFT JOIN paraCountry i ON t.UserSelect = i.CountryId
		WHERE o.ParaId = i.ParaId AND i.CountryId Is Not Null HAVING Count(*) = (SELECT Count(*) FROM #Temp))
GO

DROP TABLE ParaCountry
GO
Drop TABLE Country
GO
DROP TABLE Para
GO
DROP Table #Temp
GO

Brett

8-)

EDIT: <homer>dooooooh forgot to add the coorelation</homer>

Edited by - x002548 on 04/28/2003 15:10:47

samham
Starting Member

12 Posts

Posted - 2003-04-28 : 15:46:31

thank u X002548

i tried it and it works

in fact i have a lot of tables other than the country table and want to search in the same way, wont creating multiple temp tables affect performance ? can this be done with a table datatype for example, or with my comma seperated list of values without creating a temp table.

what am doing is building the query dynamically and then send it back to sql server to be executed, so i have a lot of search criteria before i reach this one so creating a temp table will be a little comlicated but if it's the only way i'll do it

thx again

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-29 : 09:54:15

That's the only way I could think of...certainly doesn't mean the only way.

And if you're worried about a performance hit, you will certainly get one with using dynamic sql.

As far as temp tables versus a table variable, I've seen comments in both directions. Unless you have SQL 2000 then it's not even an option. Temp tables Use tempdb, where as table datatypes are in memory. I believe the trade offs are in how you manage those resources, how much data you have, number of transactions, ect.

I wonder though how search engines do this (or do they?) Like in Google, their advanced options have"All words must match", or "At least one word must match" which is similar to what you're doing.

btw, what are you doing?

Brett

8-)

samham
Starting Member

12 Posts

Posted - 2003-04-29 : 11:59:52

am using sql server 2000 and what am doing is a search engine a little faster than google

no what am doing is a search engine for news, each paragraph is related to countries,categories etc...

i will try to use ur method by using a function which returns a table from a comma delimited list of values

am not sure if using the function and looping throught the list of values and inserting them in a table to be returned from the function is faster then using the temporary table

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-29 : 12:17:26

This is very generic, but:

If you have a list of 3 words to search for, and you can put them into a table or a table variable called "Words", you can make sure that ALL words match this way:

Select DocumentID
FROM
Documents
INNER JOIN
Words
ON
DocumentText LIKE '%' + Word + '%'
GROUP BY DocumentID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Words)

And, of course, to check if at least 1 matches, eliminate the HAVING clause:

Select DocumentID
FROM
Documents
INNER JOIN
Words
ON
DocumentText LIKE '%' + Word + '%'
GROUP BY DocumentID

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-29 : 12:56:57

Yeah, I knew there was an easier way...got myself in the over-thinking mode....

Thanks Jeff

Brett

8-)

samham
Starting Member

12 Posts

Posted - 2003-04-29 : 16:36:03

thank you very much Jeff it works great

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-29 : 16:59:27

What's cool is if you are matching more than 1 word, you can also return a matching % per document:

Select DocumentID, 1.0 * COUNT(*) / (SELECT COUNT(*) FROM Words) as MatchPct
FROM
Documents
INNER JOIN
Words
ON
DocumentText LIKE '%' + Word + '%'
GROUP BY DocumentID

and do all kinds of neat things like that.

- Jeff

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-04-30 : 14:05:30

And if you're looking for a handy way to turn that CSV list into rows in a table, so that you can then use Jeff's excellent solution, you might want to read Rob Volk's article: Parsing CSV Values into Multiple Rows

------------------------------------------------------
The more you know, the more you know you don't know.

samham
Starting Member

12 Posts

Posted - 2003-05-01 : 05:02:52

to parse my csv list i wrote the following function:

CREATE FUNCTION ParseArrayOfInt(@string varchar(100), @delimiter char(1))
RETURNS @intTable table (intElement int)
BEGIN
declare @myValue varchar(5)
declare @i int--first position of an element to extract from the csv list
declare @j int--last position of the element
set @i=1
set @j=1
while charindex(@delimiter,@string,@j)<>0
begin
set @j=charindex(@delimiter,@string,@j)+1
set @myValue=substring(@string,@i,@j-@i-1)
insert into @intTable(intElement) values(cast(@myValue as int))
set @i=@j
end
RETURN
END

NB: the list must end with the delimiter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources