|
|
Home / Groups / ColdFusion Talk (CF-Talk)
query not producing the right resultsWhat is the recordCount? Is it returning one record? Try <cfdump var="#YourQueryName#">* * > Hi there > > I have a query that is half working OK - just one part is not quite right. > I can see why at this point in time. > > Wondering if anyone can suggest chages to get it working 100%. > > Here is is: > > <cfquery name="GetDoctors" datasource="savaDB"> > SELECT > u.userid, > u.Fname, > u.Lname, > u.Company, > u.Ranking, > a.addressName, > a.address1, > a.address2, > a.city, > a.state, > a.zip, > a.phone, > a.fax, > a.addressURL, > u.SiteID, > a.isPrimary > FROM > tusers u, > tuseraddresses a > WHERE > u.SiteID = 'revitaliseyourskin' AND > u.isPublic = '1' AND > u.UserID = a.userID AND > u.Ranking IS NOT NULL > <!--- <cfif NOT IsDefined ("form.doctor") AND NOT IsDefined > ("form.practice") AND NOT IsDefined ("form.suburb")> > AND a.isPrimary = '1' > </cfif> ---> > <cfif IsDefined ("form.State") AND form.State NEQ ""> > AND a.state = '#form.state#' > </cfif> > <cfif IsDefined ("form.doctor") AND form.doctor NEQ ""> > AND u.Lname LIKE '%#form.doctor#%' > </cfif> > <cfif IsDefined ("form.practice") AND form.practice NEQ ""> > AND a.addressname LIKE '%#form.practice#%' > </cfif> > <cfif IsDefined ("form.suburb") AND form.Suburb NEQ ""> > AND a.city LIKE '%#form.suburb#%' > </cfif> > <cfif NOT IsDefined ("form.doctor") AND NOT IsDefined > ("form.practice") AND NOT IsDefined ("form.suburb")> > ORDER by u.Lname, a.addressname, a.city > <cfelse> > AND a.isPrimary = '1' > ORDER BY u.Ranking > </cfif> > </cfquery> > > Basically here is what is happening. > > > A user has displayed to them a screen with a form. The user must select a > state (other fields are optional. If a user picks only a state - the result > set displayed to the user is correct. Basically only one record per > customer is displayed in a predefined sort order. THis is based on the > isPrimary field. Only primary addresses are displayed. > > If a user selects any other field as well as state - then all records for a > customer(addresses that is) are displayed well at least are suppose to > display. For some reason only one record per customer is dispaying. > > I think it is something to do with the isPrimary field etc > > Thanks in advance for any feedbac. > > > > > > > > > |
Mailing Lists
|
Latest Fusion Authority Articles
|
