Help With A Join And Duplicates?

Hi All,


I am banging my head against a brick wall over this problem, so any
help in the correct direction would be muchly appreciated!



I have 2 SQL (MS SQL) server tables, realated to -
a Property,
Sales of that property.


A property is uniquely identifed by its Roll, valuation Number and
Suffix (not my choosing).

Each property can only appear in the property table once, and can only
have 1 assessment - but can have multiple sales (ie - over the
annalysis period the same property can sell more than once).

There is approximatly 19000 properties relating to about 8000 sales.


When creating a query to list property and most recent sale (if there
is any) I end up with somthing like this -
SELECT [roll], [valuation], [suffix], [sale date]
FROM [property]
LEFT JOIN [sales]
ON
[property].[roll] = [sales].[roll] AND
[property].[valuation] = [sales].[valuation] AND
[property].[suffix] = [sales].[suffix]
(table names simplifed).

I get rows where there is all the property data there, but sale date
(etc.) is null (as I would expect from a left join), but the problem is
- when there is more than 1 sale for a property it pulls out another
copy of the property data.

In short, because of that I come out with more records than properties.

ie -
roll valuation suffix sale date
12 456789 A 1/1/2003
12 788988 B NULL
14 123456 A 1/1/2003
14 123456 A 1/1/2004
(Note - the last two are the same property).


I didn't know that the left join can affect both joined tables!


Is there any way around this? Any suggestions/hints in the right
direction would be very much appreciated!

THANKS!