| 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 |
|
|
|
|
search query