I have two tables. One of them has 4 fields in which 3 are product codes, and 1 is the order number. The product name for each of the code can be found by referring to table 2 which has product codes and product names. I need to create a view that shows order number, product name for code 1, product name for code 2, and product name for code 3. Moreover, not every record has all three product codes. Some only have one, and some have 2. Can anyone help?

Chu