Thus spake the master programmer:
After three days without programming,
life becomes meaningless.
- The Zen of Programming

Friday, May 15, 2009

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 leftouterjoin customers c on d.id = c.deviceid

and c.id = (selecttop 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.