This is a simplified example of my problem.
I have a Product table, and a ProductSupplier table. A Product can have multiple Suppliers; these suppliers are ordered according to a sort order field and each has an associated price for the Product.
I want to run a query to return all my products, along with their first supplier and the cost against this supplier.
Currently the only way I can do this is by performing select statements in the manin select clause to retrieve the supplier information.