Best match

[Mr. Raven]

Hi, here's the question:

I have a table wich contains productinformation with a field called PRODID. Then I have another table wich is contains groupinformation. This contains a field called GROUPID, wich is the beginning of the PRODID's. A product belongs to the group with the longest matching GROUPID. For example:

+-----------+--------------+
| GROUPID | DESCRIPTION |
+-----------+--------------+
| 015543 | hats |
| 0155 | boots |
+-----------+--------------+

+-----------+--------------+
| PRODID | DESCRIPTION |
+-----------+--------------+
| 01554300 | Yellow hats |
| 0155000 | Yellow boots |
+-----------+--------------+



Say I have the value of PRODID (e.g. "01554300") and I want to select the group tou wich it belongs. In the example the returned group would be "hats" because the match "015543" is longer than the match "0155".

I also need it the other way around: I have the value of GROUPID (e.g. "0155") and I want all product wich belong to this group... It should return "Yellow boots" but not "yellow hats" because there's a group with a longer GROUPID that matches the beginning of the PRODID.

How can these two problems be solved?

Thanx!!