SQL Question

[calista]

I have the following query I am trying to correct:

<!--- Perform Queries --->
<CFQUERY NAME=&quot;GetMessages&quot;
		DATASOURCE=&quot;#Application.Datasource#&quot;
		DBTYPE=&quot;ODBC&quot;>
		SELECT		MessageID,
	     			MsgSubject,
					MsgThreadID,
					MsgDatePosted,
					MsgMessage,
					MsgForumID,
					PersonFirstName,
					PersonLastName,
					PersonEmail
		FROM 		MessageTable,
					PersonTable
		WHERE		#PreserveSingleQuotes(boolsearch)#
		AND    		MsgAuthor = PersonID
		ORDER BY	MsgDatePosted <CFIF Client.PostOrder IS &quot;Newest&quot;>DESC</CFIF>
</CFQUERY>

BoolSearch contains the following:
((MessageID Like '%97DD1C42-F841-11D4-A0AD00508BFDDBDD%')) OR ((MessageID Like '%43C5B61E-F849-11D4-A0AF00508BFDDBDD%'))

What I am trying to do is retrieve the author's name from the PersonTable based on the ID stored in the MsgAuthor column of MessageTable. What happens is, it retrieves all the people. I want two records returned, it returns 23. I use the following similar query in another place, and it works fine.

SELECT      MessageID,
	     		MsgSubject,
				MsgThreadID,
				MsgDatePosted,
				MsgMessage,
				MsgForumID,
				PersonFirstName,
				PersonLastName,
				PersonEmail
	FROM 		MessageTable,
				PersonTable
	WHERE  		MsgThreadID = '#URL.ThreadID#'
	AND    		MsgAuthor = PersonID
	ORDER BY 	MsgDatePosted <CFIF Client.PostOrder IS &quot;Newest&quot;>DESC</CFIF>

Any suggestions? Thanks!

 

[MarcDePoe]

your code seems fine to me but try this ...
(I don't know in what order and's and or's are interpretted)

WHERE (#PreserveSingleQuotes(boolsearch)#)
AND MsgAuthor = PersonID

-Marc

 

[calista]

Thanks, Marc!

That did it! AND has precedence over OR. :-D