>This must be a "DBMS 101" classic, but I don't seem to be able to figure it out:
>
>I have a discount table that has a primary key, a foreign key to customer and a foreign key to the items table. I.e. "some customers have discounts on some items".
>
>I have an order header table that contains a foreign key to the customer and order detail that has a foreign key to the item.
>
>I'm trying to build a dbms query or queries (in msaccess) that would join the discount table to the de-normalized order table (I .e. a join of the order header and the order detail on the header key).


You have to navigate from the order to the OrderDetail (inner join) , then outer join (there may or may not be a discount) to the discount table using the customer from the order and the item in the OrderDetail

select Order.*,
           OrderDetail.*, 
           Discount.Percent 
           from Order
          join OrderDetail on (OrderDetail.Order_fk = Order.pk )
          left join Discount on ( Order.Customer_fk = Discount.Customer_fk ) 
                                         and (OrderDetail.Item_fk = Discount.Item_fk )
         where ( Order.pk = ?someOrderPK)