Thanks, Mathew. That works. It looks familiar... I remember doing this
before, where it required the use of 'fake' aggregate functions on all
other columns I needed returned by the query, as well as in the ORDER BY
clause. The real query is a little more complicated.
SELECT p.propertyid,
MIN(p.adtype) AS adtype,
MIN(p.name) AS name,
MIN(p.longitude) AS longitude,
MIN(p.latitude) AS latitude,
MIN(p.address1) AS address1,
MIN(p.address2) AS address2,
MIN(p.city) AS city,
MIN(p.state) AS state,
MIN(p.zip) AS zip,
MIN(a.areacode) AS areacode
FROM properties p INNER JOIN propertyareas a ON a.propertyid =
p.propertyid
<cfif Len(form.areacode)>WHERE a.areacode = '#form.areacode#'</cfif>
GROUP BY p.propertyid
ORDER BY MIN(a.areacode), MIN(p.name)
Seems to me that there's enough 'kludge factor' in this query that I can't
help thinking either it should be designed differently or that there's
something I'm missing in the design of the database that would allow me to
do this more easily.
----- Original Message -----
From: "Matthew Walker" <[EMAIL PROTECTED]>
To: "CF-Talk" <cf-talk@houseoffusion.com>
Sent: Monday, September 05, 2005 4:50 PM
Subject: RE: Query Help
> SELECT p.propertyid, p.name, min(c.name) AS city
> FROM property p
> INNER JOIN property_city pc ON pc.propertyid = p.propertyid
> GROUP BY p.propertyid, p.name
>
> -----Original Message-----
> From: Jim McAtee [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, 6 September 2005 10:31 a.m.
> To: CF-Talk
> Subject: Query Help
>
> I have a number of real estate properties, each associated with one or
> more city. If I select by specifying a city then I get a list of unique
>
> properties. But if I do a broader select, then I get duplicates of the
> property record when a record is assocuated with two or more cities.
> How
> do I limit the select results returned so that I get no more than one of
>
> each property record?
>
> SELECT p.propertyid, p.name, c.name AS city
> FROM property p
> INNER JOIN property_city pc ON pc.propertyid = p.propertyid
>
>
> property
> --------------
> propertyid
> name
>
> city
> --------------
> cityid
> name
>
> property_city
> --------------
> propertyid
> cityid
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217396
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
