>>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
>
>Chu, if you are working in VFP5 then you can set up the SQL using the "left outer join" syntax. However, you will probably have to do it outside a view and then use Create View to put the view in the DBC. I don't think the View wizard will handle anything this complicated. You may also have to have the product table opened under separate aliases for the different codes or use a series of SQL-Select statements. If this is true, then you may need to put the code in a PRG or a method instead of a view.
>
>HTH
>Barbara

Barbara, creating a cursor will be fine for my purpose. So, I have the following code.

SELECT a.order_num, b.p_name as p_name1, b.p_name as p_name2, b.p_name as p_name3, ;
FROM order a ;
LEFT JOIN prod_dct b ON a.p_code1=b.p_code ;
LEFT JOIN prod_dct b ON a.p_code2=b.p_code ;
LEFT JOIN prod_dct b ON a.p_code3=b.p_code ;
INTO CURSOR order

It is clear that the above code doesn't work. I just don't know how to get the three different product names shown on the same record. Please help! Thanks.


Chu