SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 search query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

samham
Starting Member

12 Posts

Posted - 04/28/2003 :  12:43:22  Show Profile  Reply with Quote
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

USA
547 Posts

Posted - 04/28/2003 :  13:46:52  Show Profile  Reply with Quote
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
Go to Top of Page

samham
Starting Member

12 Posts

Posted - 04/28/2003 :  14:32:19  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/28/2003 :  15:06:21  Show Profile  Reply with Quote
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
Go to Top of Page

samham
Starting Member

12 Posts

Posted - 04/28/2003 :  15:46:31  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/29/2003 :  09:54:15  Show Profile  Reply with Quote
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-)
Go to Top of Page

samham
Starting Member

12 Posts

Posted - 04/29/2003 :  11:59:52  Show Profile  Reply with Quote
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

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2003 :  12:17:26  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/29/2003 :  12:56:57  Show Profile  Reply with Quote
Yeah, I knew there was an easier way...got myself in the over-thinking mode....

Thanks Jeff



Brett

8-)
Go to Top of Page

samham
Starting Member

12 Posts

Posted - 04/29/2003 :  16:36:03  Show Profile  Reply with Quote

thank you very much Jeff it works great
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2003 :  16:59:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 04/30/2003 :  14:05:30  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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.
Go to Top of Page

samham
Starting Member

12 Posts

Posted - 05/01/2003 :  05:02:52  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000