Jason - thanks for your help, I couldn't see the obvious!

If changed the WHERE clause to

(products.labsoutputproduct is not null or products.labsoutputtint is not null)

and it works!

🙂

Thanks for the reply, but the problem still exists. For example, one of the orders that appear in the result should have 2 outputs (as shown below)

select orderdetails.orderid, orderdetails.productid, orderdetails.qty,...

Thanks for all your input.

John was correct that Product table was no longer required (I had used it earlier, but didn't need it any longer).

Scott - your solution looks perfect!...

Ken, this seems to have done the trick! Thanks for your help!