Hi everybody,

We have two tables: Billing_Charges and VisCodes. VisCodes is a big lookup table with all possible code values for different purpose. So, if we want to select certain records we use cCategory_Description field.

In Billing_Charges we have cRevenue_Code field. This field is 4 characters long. In VisCodes the generic cCode_Value field is 10 chars long. When the Billing_Charges table was designed it was decided to put code field rather than PK field from VisCodes, because there could be codes that don't exist in VisCodes.

So, we're trying the following SQL:

SELECT Billing_charges.*, Viscodes.ccode_description;
 FROM ;
     Billing_charges ;
    LEFT OUTER JOIN VisCodes ;
   ON  Viscodes.ccode_value LIKE ( Billing_charges.crevenue_code+"%" );
 WHERE  Billing_charges.cbilling_main_fk = ( ?vp_cBilling_main_fk );
   AND  Viscodes.ccategory_description LIKE ( "REVENUE_CODES%" )

However, it doesn't work if we put non-existant value in cRevenue_Code field.

How does LEFT JOIN work? Does it work based on the order of the tables in SQL or based on the JOIN expression itself? Is there a way to fix this SQL without using SUBSTR or PADR function? The VisCodes table is relatively small, the Billing_Charges table is currently small (just few records), but has potential to grow...

Thanks in advance.