Help with "search page" 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

SQL Server Development (2000)

Help with "search page" query

Author

Topic

Kolten
Starting Member

4 Posts

Posted - 2004-05-12 : 18:56:53

Hello, I have a pretty neat little query builder I built here:

It's an ASP page.
Now, I need to incorporate the "AND/OR" and the "Contains/Does Not Contain" fields into a query that I built.
Here is the ASP that I have to build the query:


	numFields = request.form("numFields")
	
	FOR i = 1 to numFields
		compare = request.form("COMPARE"&i)  'contains "IN" or "NOT IN"
		andor = request.form("ANDOR"&i)		'contans "AND" or "OR"
		category = request.form("CategorySelect"&i) 'contains ID of category (Table contactfields)
		field = request.form("SubCategorySelect"&i) 'contains ID of item selected (table ContactFieldData)
		
		if i = 1 then
			sql = "SELECT ContactID FROM ContactFieldRelation WHERE FieldDataID IN ("&field
		else
			sql = sql & ", "&field
		end if
	NEXT
	sql = sql & ") GROUP BY contactID HAVING count(*) = "&numFields
	response.write(sql&"<HR>")
	Set rs = conn.execute(sql)
	DO while NOT rs.eof
		sql = "SELECT * FROM Data WHERE DataID = "&rs("contactID")
		Set rs2 = conn.execute(sql)
		response.write(rs2("FirstName")&" "&rs2("LastName")&"<BR>")
		rs.movenext
	LOOP

and here is a sample query (based from TWO selections):


SELECT ContactID FROM ContactFieldRelation WHERE FieldDataID IN (7, 28) GROUP BY contactID HAVING count(*) = 2

Finally, here is the table:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ContactRelationID FieldDataID ContactID
27 39 1931
28 43 1931
29 44 1931
30 7 1931
31 26 1931
32 28 1931
33 39 1932
34 7 1932
35 26 1932
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Now, as you can see, I am using the IN operator to pull the ContactID for the criteria the user selects. Note that I only want to pull the ContactID if ALL the criteria matches, and it does so with the count(*) = 2 option.
What I am next wanting to do is incorporate the "AND/OR" operators and the "Contains/Does Not Contain" (IN and NOT IN) operators, but I don't have a clue how to do so.
Could someone get me started into what I should be looking to do here? Thank you very much for reading.

~~Kolt

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 19:11:19

You're doing a DB call for every record returned in the 1st set (Set rs = conn.execute(sql))? What if there are 100,000 rows returned? YIKES.

How bout calling the data like this:


	FOR i = 1 to numFields
		compare = request.form("COMPARE"&i)  'contains "IN" or "NOT IN"
		andor = request.form("ANDOR"&i)		'contans "AND" or "OR"
		category = request.form("CategorySelect"&i) 'contains ID of category (Table contactfields)
		field = request.form("SubCategorySelect"&i) 'contains ID of item selected (table ContactFieldData)
		
		if i = 1 then
			sql = "SELECT ContactID FROM ContactFieldRelation WHERE FieldDataID IN ("&field
		else
			sql = sql & ", "&field
		end if
	NEXT
	sql = sql & ") GROUP BY contactID HAVING count(*) = "&numFields
	sql = "SELECT * FROM Data WHERE DataID in (" & sql & ")" 
	response.write(sql&"<HR>")
	Set rs = conn.execute(sql)
	DO while NOT rs.eof
		response.write(rs("FirstName")&" "&rs("LastName")&"<BR>") 
		rs.movenext
	LOOP

Daniel
SQL Server DBA

Kolten
Starting Member

4 Posts

Posted - 2004-05-12 : 19:20:04

Very nice! I was wondering about that myself, but was unsure how to go about it.
Now, about my "AND/OR" and "IN/NOT IN" question... Any idea where I can stick those in so that when the user selects "OR" or a "DOES NOT CONTAIN" from the dropdowns, the sql will make sense? Even a starting point, or common practice to get me started. I don't have a starting point right now.
Thank you very much!

~~Kolt

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 19:21:04

ok for the where clause stuff. I dont remember asp much been messing with .net for now.

In ASP can your combo boxes have both a text and a value? In other words can you set the text that the user sees to "engine modifications" and then set the value of that selecteditem to the column name of the table? If yes, you would end up with something like this:

sql = "SELECT ContactID FROM ContactFieldRelation WHERE " & combobox1.selecteditem(i).value & " = '" & combobox2.selecteditem(i).text & "'"

On a side note I think there is a good article either on here or another sql site that talks about using the Coalesce function in stored procs. I would look into that. You'll probably end up redoing your entire page after reading about it.

After thinking more about this, if you do choose to stay with this method, I would probably make a few strings: one for OR and one for AND. I would concat the or stuff together and concat the and stuff together then concat them both into the SQL string at the end. This way you dont have to fuss with the '()' signs. You'll be able to just toss " and (" on the front and ")" on the end of your OR string then concat it to the and string.

Daniel
SQL Server DBA

Kolten
Starting Member

4 Posts

Posted - 2004-05-12 : 19:32:58

Well, the only value I need is the ID, which is passed from the client when they select from the combobox. The statement after the WHERE clause wouldn't make sense because I will always be pulling from the FieldDataID column. Column names aren't part of the selection process in my little asp page.
Basically, I am looking for a way to incorporate "AND" or "OR" (according to what the user chooses in the dropdown as they add items to the query list using the little "+" symbols - see graphic) and the "CONTAINS/DOES NOT CONTAIN" which hold the value of "IN" and "NOT IN". I want to mix these selections in with my current sql querystring somehow, but am not even sure where to start. I've tried this:


		if i = 1 then
			sql = "SELECT ContactID FROM ContactFieldRelation WHERE FieldDataID "&compare&" ("&field
		else
			sql = sql & ", "&field&") "&andor&" FieldDataID "&compare&" ("&field
		end if

...but obviously was a shot in the dark for me :)
thank you for your help

~~Kolt

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 19:56:56

Well the problem is that you need an SQL statement that looks like this:

select * from table where column = 'data' and column = 'data2' and (column2 = 'data3' or column2 = 'data4')

You need the "OR" put into ()'s to keep it from jacking with your "AND"'s. Just make 2 strings and concat the user made selections that you want to filter by into those 2 strings. Use one string for the AND and one string for the OR. Then you will concat those like this:

sqlwhere = "where " & sqland & " " & sqlor

sqlor should look like this "and (column2 = 'data3' or column2 = 'data4')"

Hope that helps cause I dont think I can explain it any better. I'm no asp master. Now if you wanna talk replication thats a different story.

Daniel
SQL Server DBA

Kolten
Starting Member

4 Posts

Posted - 2004-05-12 : 20:03:48

Oh i think I get it.
I was mixing my 'p's with my 'q's :)
I will try something tonight. Thank you very much for you help!

~~Kolt

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 20:06:21

quote:
Originally posted by Kolten

Oh i think I get it.
I was mixing my 'p's with my 'q's :)
I will try something tonight. Thank you very much for you help!

~~Kolt

Crazy Canadians... always forgetting to keep their p's separate from their q's.

Daniel
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources