Complicated Joins In Database

[leeb1977]

Hi, I hope you can help, I may be completly over thinking this, but I
am stuck...

I've a database with several tables linked.

Two of the tables are linked by product code.

Table 1 is Sales Forecast
Table 2 is Bill Of Materials for the products

So, by linking the two, I have the forecasted requirement for the bill
of materials.

Now, here's my problem.

There are a load of products that have a forecast, where the Bill Of
Materials, for that product, is another Product.

For these Products, the Product Code is AB1234-5678
For normal products that do not have this issue the product code is
AB1234

So, the way I see it would be that I have some kind of condition in
the database that says

Join Product Code - Product Code

If Product Code has a Hyphen and Additional 4 numbers, then find the
Product Code without the hyphen and last four numbers, and apply the
rule as above.

Or something like that???

As I say, it might not be possible, or I might be completely going
about this the wrong way, but any help would be appreciated!!

Thanks,

Lee

 

[ghetto_banjo]

If you product code is always in the format of X characters OR X
characters followed by hyphen, you could make the WHERE condition of
the SQL just look at those first X characters.
For example if you want to compare just the first 6 characters:


SELECT Table1.ProductCode, Table1.Description
FROM Table1, Table2
WHERE (Left([table1].[ProductCode],6) = Left([table2].[ProductCode]));


If it is a variable number of characters before the hyphen, it is
still probably possible but you would need to use some more complex
string functions.