I think Jim wants to output just one row even if there is more than one city. So the city that is output is an arbitrary selection from the associated cities. Your solution still outputs one row for each city.
-----Original Message----- From: Ryan Guill [mailto:[EMAIL PROTECTED] Sent: Tuesday, 6 September 2005 10:45 a.m. To: CF-Talk Subject: Re: Query Help SELECT DISTINCT p.propertyid, p.name, c.name AS city FROM property p INNER JOIN property_city pc ON pc.propertyid = p.propertyid DISTINCT is all you need. On 9/5/05, Jim McAtee <[EMAIL PROTECTED]> wrote: > 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:217393 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
