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 > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217390 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
