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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315414
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
