Perhaps more elegant....
SELECT p.adtype,
p.name,
p.longitude,
p.latitude,
p.address1,
p.address2,
p.city,
p.state,
p.zip,
(
SELECT TOP 1
Areacode
FROM propertyareas a
WHERE a.propertyid = p.propertyid
) AS areacode
FROM properties
ORDER BY p.name
-----Original Message-----
From: Jim McAtee [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 6 September 2005 11:46 a.m.
To: CF-Talk
Subject: Re: Query Help
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" <[email protected]>
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
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217401
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
