Top 1 in a subquery…

So I came across an interesting sql problem.  I have a list of devices that could have multiple customers attached to the device.  And on the admin page, i need to list the devices and at least one customer attached to the device.

I didn’t want to display multiple rows in the table each with the same device but multiple customers.  This is how I solved it:

select * 

from devices d left outer join customers c on d.id = c.deviceid 

        and c.id = (select top 1 id from customers where deviceid = d.id)

 

The first left outer join, insures that if i have a device with no customers, I’ll still get the device results back.  If I have multiple customers, I’ll get the first one returned.  I could put other “filtering/sorting” in the subquery if i wanted, but this seemed to work enough for my purposes.