More on JOINS
Subject:   Join for more than one values in foreign key field.
Date:   2005-06-08 08:28:47
I am trying to join two tables but the foreign key table can have exact value in all the foreign key fields or some of the foreign key fields may not as in primary key fields. I want the join condition to return the record of foreign key table, the maximum number of foreign key fields of which matches with the primary key fields. How can I do it. The foreign key fields which wont have exact values, will have star in it.

e.g. The foreign key table has three records for Country=India and two records for State=Punjab but the third record has State=*, one record has City=Chandigarh, second record has City=Ludhiana and third record has City=*. Now when I try searching for record having Country=India and State=Delhi and City=Delhi, I expect the record from foreign key table having Country=India and State=* and City=* but when I try searching the record with Country=India and State=Punjab and City=Chandigarh then I expect the exact record exists in foreign key table.

Please guide me to come out of this trap