Not tested this out on the tables, but think it will work.
select p.prod_name, o.prod_id from OrderDetails o, Products p
where o.order_id in (
select order_id from OrderDetails
where prod_id = @prodid)
and o.prod_id = p.prod_id
and o.prod_id <> @prodid
Replace @prodid with the product id the customer selects.
This will return the product name and the product id in case you require it later on in your code.
You can even enhance this by using 'top 10' outside this query to pick the most popular of the products. See BOL for info on how to do that. It's fairly simple.
------------------------------
The Users are always right - when I'm not wrong!