I've been grappling with this one for a while now, and I finally came up with a solution, so I figured I better post this someplace because when it comes time for me to do it again, I need to remember how!
Sometimes you want to get a list of records, and join them with another table, but with one-to-many relationships, this will result in records from the first table showing more than once. Here's an example:
We have two tables, Customer and Address. Each customer can have multiple addresses. I want to get a list of all our customers and list their corresponding address next to them. For customers with multiple addresses, I just want to show one of their addresses. In fact, in my address table, I have a bit indicating a default address. Let's just show their default address.
If we query a simple join:
select * from Customer C left join Address A on C.customer_ID = A.customer_ID
This works, but customers with multiple addresses are returned over and over, once for each of their addresses.
The answer is to include a top statement in a subquery in the ON clause.
select * from Customer C left join Address A ON C.customer_ID=A.customer_ID AND A.address_ID in (select top 1 AA.address_ID from Address AA where AA.customer_ID=C.customer_ID order by DefaultAddress desc)
If address records are optional, you can also add clauses that allow it to be null too, but this is a simplified version.