hi there! i am trying to create a process to bring information from four different tables. I tried to create a relation, but it doesn't work. I am not too familiar with JOINs in SELECT-SQL.

One table has 1 field (item_code) ascending (15 records)
Two table has extracted information from a larger table from an earlier SQL process that i used (credits). (invoice numbers about 1000)

Third Table has current information that I am searching on (300,000 records)
Fourth Table has archived information that I am searching on. (1.5 mil records)

Essentially, I am needing to seek for the combined information from table one and table two from the data in the 3rd and 4th table.

The problem with relations is that the 2nd table's field that I am using is SUBSTR for the data information.

Now the combined data from table 1 and 2 are not necessarily in table 3 thus needs to seek in table four.

For example:

Table 1 - Item code: RA
Table 2 - Invoice Numer 'CREDIT' and DESC = M123456, Mr. Stone
Table 3 - Information is not found
Table 4 - Information is found but listed as Invoice number M123456 with Description as item codes (different item codes for the same invoice number, but we are trying to find 'RA')

I hope this is clear enough...i am boggled. I am trying to do a SQL with a join, but I am not sure that I am getting it right. Thanks in adavnce.