Help!!!!!

[Sarky78]

Hi All,

This is going to be quite tricky to explain, but hang in there please.....

Ok, so we are using a third party system that uses key value pairs within a database table. The basic structure is ID, SubID, Name, Value. There are multiple subID's which build up meta data about a specific thing. One of these fields is a comma seperated list of values. Now what i need to be able to do is to return all of the values from the table, but to then use the values of the comma seperated list within the query itself as part of a where statement.

At the moment because of the format of the table i have used a temporary table which is populated by various small queries and i am then using this temporary table as my base for my search.

The issue that i have is referencing this this comma list within my query.

what i ahve at the moment is:

creation of temp table

select a,b,c
from tableA
where ID IN (
SELECT ID
from tempTable
where fieldA = '%aa%'
loop over commaList
AND fieldA = '%commaListValue%'
end
)

i have tried refernecing it via the tablename.fieldname convention (errored), creating a function that will return the value (but i couldn't reference the ID i needed to identify the commalist uniquely) and various other things, now i am here hoping someone out there can help out with this and stop my brain melting.

I would have posted the code, but there is about 173 lines of it

Any help appreciated

Thanks

Tony

 

[bborissov]

Could you post some example data and desired result from it?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[bborissov]

Maybe, just MAYBE:

select a,b,c
from tableA
where ID IN (
  SELECT ID
  from tempTable
  where fieldA = '%aa%' -- Are you sure you didn't want LIKE instead of [=]?
    AND CHARINDEX(fieldA, commaListField)<> 0)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[SgtJarrow]

So you have comma-separated list of values and want to pull all records that match one of those values??? Kind of like:

SELECT
    somefields
FROM tablename
WHERE somecolumn IN ('a','b','c','d',...)

And that 'a','b',... list is a single string-type value??

If so, I have used a Split function for this...Check out this page:

http://www.codeproject.com/KB/database/splitparameterstring.aspx

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[gmmastros]

There is a trick that might help you. Please realize that it can be slow because it's not possible for indexes (that may exist on your table) to help speed up the query. Let me explain...

Most people are familiar with the LIKE keyword. LIKE allows you to do partial matches. For example...

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('apple')
Insert Into @Temp Values('banana')
Insert Into @Temp Values('grape')
Insert Into @Temp Values('pineapple')
Insert Into @Temp Values('grapefruit')

Select *
From   @Temp
Where  Data Like '%apple%'

The query above will return 2 rows (apple & pineapple) as you expect. You see, with a like search, the 'big' string is on the left and the smaller string (with the pattern matching) is on the right. This explains why pineapple is returned when searching for '%apple%'. So, we are accustomed to putting the field on the left and the pattern on the right, but it doesn't have to be that way. For example...

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('apple')
Insert Into @Temp Values('banana')
Insert Into @Temp Values('grape')
Insert Into @Temp Values('pineapple')
Insert Into @Temp Values('grapefruit')

Select *
From   @Temp
Where  'pineapple' like '%' + Data + '%'

This will also return 2 rows because there are 2 rows in the table the satisfy the like condition.

So, how does this help us? Well, if we use the comma separated list on the left and match it to the value in the table, we can probably get it to return the correct data. For example...

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('apple')
Insert Into @Temp Values('banana')
Insert Into @Temp Values('grape')
Insert Into @Temp Values('pineapple')
Insert Into @Temp Values('grapefruit')

Declare @CommaList VarChar(1000)

Set @CommaList = 'apple,grapefruit'

Select *
From   @Temp
Where  @CommaList like '%' + Data + '%'

When you run the code above, you get 3 items returned (apple, grape, and grapefruit). This is not exactly what we want, but it is close. You see, 'grape' is found within the string 'apple,grapefruit'. This is a minor inconvenience, but not really a problem. All we need to do is to be a little more careful about our commas. If our search string looked like ',apple,grapefruit,' and we searched for '%,grape,%', we would NOT match (because of the commas) and we would have our query. So....

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('apple')
Insert Into @Temp Values('banana')
Insert Into @Temp Values('grape')
Insert Into @Temp Values('pineapple')
Insert Into @Temp Values('grapefruit')

Declare @CommaList VarChar(1000)

Set @CommaList = 'apple,grapefruit'

Select *
From   @Temp
Where  [!]',' +[/!] @CommaList [!]+ ','[/!] like '%[!],[/!]' + Data + '[!],[/!]%'

Like I said earlier, this code may be too slow for you, but I do encourage you to apply this logic to your query and give it a shot. You may be pleasantly surprised.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Sarky78]

George, Thanks for the suggestion, I'll give that a whirl.
Borislav, sorry, that was a typo, from code i was just typing in, its not an extract of the actual query as too big.

this is a cut down block:

	CREATE TABLE #RecipeSearchTable (PageID int,  Condition nvarchar(2000), course nvarchar(2000), Requirement nvarchar(2000), Title nvarchar(2000), searchTerms nvarchar(2000))

	INSERT INTO #RecipeSearchTable (PageID, Condition, course, Requirement, Title, searchTerms)
    	SELECT p.p_page_id,
		(SELECT ME1.FieldValue
		 FROM META_Extended AS ME1
		 WHERE (ME1.MetaID = 20)
		 AND (ME1.FieldName = 'condition')
		 AND ME1.PageID=p.p_Page_ID
		) AS Condition,

                --code removed from here, but you get the idea
                -- this is the comma seperate field
                -- contains info like apple,pear,orange
		(SELECT ME6.FieldValue
		 FROM META_Extended AS ME6
		 WHERE (ME6.MetaID = 20)
		 AND (ME6.FieldName = 'searchTerms')
		 AND ME6.PageID=p.p_Page_ID
		) AS searchTerms

	FROM Page_Data p
	WHERE md.MetaID = 20

	DECLARE @lclSearchString as varchar(5000)

	SET @lclSearchString = 'SELECT p.P_Page_ID, p.P_Content, '
        --this returns the searchterms as needed/expected
	SET @lclSearchString = @lclSearchString + ' (SELECT dbo.myTestFunction(p.p_page_id, ''searchTerms'', 20)) as Testing, '
        
	SET @lclSearchString = @lclSearchString + ' FROM pageData p '
	SET @lclSearchString = @lclSearchString + ' WHERE p.p_page_id IN ( SELECT PageID FROM #RecipeSearchTable '
	SET @lclSearchString = @lclSearchString + ' WHERE 1=1 '

	if (@i_SearchTerm IS NOT NULL)
	begin
		SET @lclSearchString = @lclSearchString + ' OR (Condition LIKE ''%' + @i_SearchTerm + '%'')'
		SET @lclSearchString = @lclSearchString + ' OR (course LIKE ''%' + @i_SearchTerm + '%'')'
		SET @lclSearchString = @lclSearchString + ' OR (Requirement LIKE ''%' + @i_SearchTerm + '%'')'
		SET @lclSearchString = @lclSearchString + ' OR (Title LIKE ''%' + @i_SearchTerm + '%'')'

		--this is where i need to be able to access the searchTerm element from the temp table, to be able to add it into the query as additional OR statements.  The code below is code i have used before to loop over each element of the list to create the additional statemet
/*if (Len(searchTerms) > 0)
		begin
			DECLARE @MetaLength as integer
			DECLARE @CommaCount as integer
			DECLARE @MetaValue as varchar(500)
			SET @MetaLength = Len(@MetaAdditional)
			While @MetaLength > 0
			begin
				SET @CommaCount = patindex('%,%', @MetaAdditional)
				if @CommaCount > 0
				begin
					SET @MetaValue = SubString(@MetaAdditional, 1, (@CommaCount-1))
				end
				ELSE
				begin
					SET @MetaValue = @MetaAdditional
				end

				SET @lclSearchString = @lclSearchString + ' OR (recipeTitle LIKE ''%' + @MetaValue + '%'')'

				IF @CommaCount > 0
				BEGIN
					SET @MetaAdditional = Right(@MetaAdditional, (@MetaLength-@CommaCount))
				END
				ELSE
				BEGIN
					SET @MetaAdditional = '';
				END

				SET @MetaLength = Len(@MetaAdditional);
			end
		end*/

		SET @lclSearchString = @lclSearchString + ')'
	end

	SET @lclSearchString = @lclSearchString + ')'

	
END

Thanks all for your time so far, going to try a couple of things you have suggested now. hope the above makes sense, if not let me know and i'll try and clarify

Tony

 

[Sarky78]

Additional:

what i should have said is the outcome that i want is:

select ...
....
where PageID IN (
.
.
.
.
.
(OR (Title LIKE '%biscuit%')
OR (Title LIKE '%apple%')
OR (Title LIKE '%pear%')
OR (Title LIKE '%orange%')
)

)

Tony