Joins with multiple columns

[Anonymous]

Hi All,

I have 2 tables named Policy and Client.

I want to make a join between the above 2 tables  based on 2 different columns from Policy and 1 column from Client table like

Client.Client_ID = Policy.First_User_Id and

Client.Client_ID = Policy.RO_Name_Id

But as we all know we can't have join the two tables based on 2 columns so I used this approach -

I created 2 additional column in each table like -

Policy Table -

Autonumber(PO_FIRST_USER_ID,[RO_NAME_ID]) as 'Client ID'

Client Table -

Autonumber(CL_CLIENT_ID,CL_CLIENT_ID) as 'Client ID'

Please suggest if this is the right approach or the above requirement can be implemented in some other way.

[Anonymous · Accepted solution]

your Approach would be okay, if Policy.First_user_id and Policy.RO_Name_id are always identical.

I donot know your Business, but Policys can have different persons (and therefor different ids). Insurance policys might have the insured Person and a Person who pays. If These persons are not identical you will not get a match.

An Approach might be to duplicate the Client table and Name Client id to RO_Name_id (which is for sure not best Approach as it takes additiional space)

[maxgro]

also

Client.Client_ID & '|' & Client.Client_ID as key

Policy.First_User_Id & '|' & Policy.RO_Name_Id as key

[Anonymous]

Thanks Rudolf for the answer and logic. Your logic seems to be correct we can have different FIRST_USER_ID and RO_NAME_ID and thus creating alias of Client table should be the suitable approach here.

[Anonymous]

would this act same as what I implemented ?

[maxgro]

yes, try, reload and open the table viewer (ctrl+t); you should see the 2 tables associated (join) by the field key