Limiting results of joins

[AndyHopper]

I have a table with some property information, another table with some more information about those properties, a table which contains the relationships between media files and properties and a table to contain media. When I use joins to get the information I'm looking for I return more rows than I'd like. For example a simple search may yield:

Boston, MA - $2,000,000 - pic1.jpg
Boston, MA - $2,000,000 - pic2.jpg
Boston, MA - $2,000,000 - pic3.jpg
Needham, MA - $1,000,000 - pic2.jpg
Needham, MA - $1,000,000 - pic3.jpg

Here's the query I'm currently using:

SELECT some fields
FROM property LEFT OUTER JOIN
property_ext ON property.pid = property_ext.pid LEFT OUTER JOIN
media INNER JOIN
media_rels ON media.media_id = media_rels.media_id ON property.pid = media_rels.pid

How can I limit the results to one row per unique property?

Thanks in advance,
Andy

 

[ThomVF]

This works in ORACLE...

select t.pict, t.dollars, t.city
from test t,
(select city, dollars, pict
from test where rownum = 1) t2
where t.city = t2.city

Try this SQL-Server version:

select t.pict, t.dollars, t.city
from test t,
(select TOP 1 city, dollars, pict from test) t2
where t.city = t2.city

"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com