Relate field visible in studio not found in database

Question

Hi all! 
 Where can I find relate fields in the database? 
 In studio we have a custom relate field on account named merged_into_c, which contains a link to another account. 
 I want select the accounts which were merged into another account, but the field merged_into_c does not exist in the database, not on the accounts and not on the accounts_cstm table. There is also no accounts_accounts or accounts_merged_into table. It is not clear to me where this relation is stored in the database... 
 SELECT Id, name FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c WHERE accounts_cstm.merged_into_c IS NOT NULL AND NOT accounts_cstm.merged_into_c='' LIMIT 10; 
 Anyone any idea how this information is stored? 
 Regards, 
 Stijn

Stijn Vermeulen · Accepted Answer

Hello Stijn Vermeulen , 
 When a relate type field is created Sugar will add an ID type field on the module cstm table. 
 For your example, when the merged_into_c is created in Studio account_id_c is created on the accounts_cstm table to store the ID's of the related accounts. 
 You can double check the mappings of merged_into_c and the id field in the fields_meta_data table: 
 SELECT name,ext3 FROM `fields_meta_data` where name='merged_into_c'; 
 If this is the only relate field that you have relating to the Accounts module your query should be something like this: 
 SELECT id, name FROM accounts LEFT JOIN accounts_cstm 
ON accounts.id = accounts_cstm.id_c 
WHERE accounts_cstm.account_id_c IS NOT NULL 
AND NOT accounts_cstm.account_id_c='' LIMIT 10; 
 
 I hope this helps 
 
 Andr&eacute;