Random Join in Sql Server

Question

I have two table that called Product and ProductImage.

There is 1-n relation between two table. One product and more than one image depending on the product.

I want to create a product view and I want to get one image randomly from ProductImage table for each product.

Example Data : http://sqlfiddle.com/#!6/43c69

I want something like below.

+-----------+------+-------------+
| ProductId | Name |   WebPath   |
+-----------+------+-------------+
|         1 | Foo  | foowebpath2 |
|         2 | Boo  | boowebpath3 |
|         3 | Zoo  | zoowebpath1 |
+-----------+------+-------------+

or

+-----------+------+-------------+
| ProductId | Name |   WebPath   |
+-----------+------+-------------+
|         1 | Foo  | foowebpath1 |
|         2 | Boo  | boowebpath1 |
|         3 | Zoo  | zoowebpath6 |
+-----------+------+-------------+

or

+-----------+------+-------------+
| ProductId | Name |   WebPath   |
+-----------+------+-------------+
|         1 | Foo  | foowebpath4 |
|         2 | Boo  | boowebpath2 |
|         3 | Zoo  | zoowebpath5 |
+-----------+------+-------------+

or etc...

It have to be different each time.

Answer 1

You should try this

SELECT *, (SELECT TOP 1 WebPath FROM ProductImage PI 
WHERE PI.ProductId = P.ProductId order by NEWID()  ) as WebPart from Product P

Check this fiddle http://sqlfiddle.com/#!6/43c69/16