?
Solved

Query/SQL question

Posted on 2006-06-01
4
Medium Priority
?
194 Views
Last Modified: 2013-12-24
Something is wrong with my SQL in this query. I'd like to be able to include various WHERE conditions based on form input. Problem is, when a WHERE condition is included it returns 0 records. I've tested only the WHERE p.Active = 1 condition so far -- that should return 9425 records but returns 0. If I comment out the WHERE condition, it returns the correct number of total records, 9532.

select p.PK_PersonsID
      , concat(COALESCE(p.FirstName, ' '),' ',COALESCE(p.LastName, ' '), ' ',COALESCE(m.MBREADesignation, ' ')) as name
      , p.Company
      , p.Address1
      , p.Address2
      , p.City
      , p.State
      , p.County
      , p.Zip
      , p.Active
FROM tbl_persons p LEFT OUTER JOIN tbl_members m
    ON p.PK_PersonsID = m.PersonsID_FK
WHERE p.Active = 1
<cfif isDefined("form.sortCounty")>
and p.County = '#form.County#'</cfif>
<cfif isDefined("form.sortState")>
and p.State = '#form.State#'</cfif>
<cfif isDefined("form.sortMembership")>
      <cfif form.Membership EQ "All">and p.MemberType_FK > 0<cfelse>
      and p.MemberType_FK = #form.Membership#</cfif>
</cfif>
<cfif isDefined("form.sortorder")>
order by #form.printorder#</cfif>
0
Comment
Question by:brucecrabtree
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:bwasyliuk
ID: 16811181
If you are using SQL Server, you could run a Trace while your page executes, and see the actual syntax that is getting passed to the DB.
0
 

Author Comment

by:brucecrabtree
ID: 16811201
I'm using MySQL
0
 
LVL 7

Accepted Solution

by:
bwasyliuk earned 2000 total points
ID: 16811251
Try doing a <cfoutput> around your query instead of a <cfquery>, which will give you the sql code that is being generated.  Post the CFOUTPUT and we can go from there.
0
 

Author Comment

by:brucecrabtree
ID: 16811340
Thanks -- I didn't know that trick. That's incredibly useful! I can see what the problem is now.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question