SQL Server Performance Forum – Threads Archive

search query

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

samham – it is not fully clear what you are trying to achieve. Maybe the user of ALL ANY is a bit confusing. Try to explain better and maybe one of us could help <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Bambola.

ok here’s what i want the user can choose a list of countries and get back paragraphs related to these countries what i mean by RELATED is: 2 possibilities: - if ANY of the countries he chooses is related to a paragraph the paragraph is selected so if paragraph 1 is related to countries 1,2,3 and the user chooses 2 for example (or 2,4 etc..) the paragraph is selected becoz 1 of the countries he selected appears in the paragraphs countries. this was the first possibility.. this is the easy one it works fine with the code i listed before (by the way this code was suggested by u bambola in a previous thread <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ) - the second possibility is that paragraphs r selected only if ALL the countries chosen by the user r included in the paragraphs countries in the paraCountry table. so for the previous example the paragraph is returned only if the user chooses as countries 1 or 1,2 or 1,2,3 and other than this the paragraph wont be returned hope this make it more clear NB: if it’s possible to do it without temporary tables it will be a lot better coz am building the query dynamically and then send it back to sql server and i have a lot of search criteria like this one so i’ll have to create a lot of temporary tables and performance and speed are important.

Ok, I understand you now. Basically you had the countryID in OR and now you need them in AND.
What I would do is write a little function to parse the string and return a table datatype
CREATE FUNCTION dbo.ParseArrayOfInt(@string varchar(8000), @delimiter char(1))
RETURNS @tResults table (I_Element int)
BEGIN
— Loop over the string with charindex(@delimiter, @string)
— and insert into the table the values. RETURN
END — Join the 3 tables as follows.
SELECT a.paraID, a.paraTitle
FROM @para a
INNER JOIN @paraCountry c
ON c.paraID = a.paraID
INNER JOIN dbo.ParseArrayOfInt(‘1,2,3’, ‘,’) s
ON s.I_Element = c.countryID HTH Bambola.

i tried it and gave me a OR result not an AND result so it’s identical to the query in the first post i obtained results if ANY of the chosen elements r present not ALL

i added the following to ur query and it works fine:
having count(*)>=(select count(*) from dbo.ParseArrayOfInt(‘62,95,105,’, ‘,’)) since i know the exact number of elements from my app when building the dynamic query it will be: having count(*)>=3
thank u

it’s having count(*)=3 not having count(*)>=3

]]>