SQL: Joins that limit results from one of the tables

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.