Several times recently I had to created what is basically random assoications between two sets of tables. The problem is usually trying to get some data for the UI. Here is what I came up with:

The only interesting part is the where clause, since it forces SQL Server to evaluate the statement on a row by row basis. This has horrible performance, by the way.