Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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)
also
Client.Client_ID & '|' & Client.Client_ID as key
Policy.First_User_Id & '|' & Policy.RO_Name_Id as key
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.
would this act same as what I implemented ?
yes, try, reload and open the table viewer (ctrl+t); you should see the 2 tables associated (join) by the field key