Identifying Unique Account for Each Contact

[kbocek]

Using SugarOS 4.5.1d on linux

I'm needing to write some custom queries in ZuckerReports and need to understand how Sugar relates the various tables. I'd like to know how Sugar identifies the Account Name that is associated with each contact. I understand that the accounts_contacts table provides the "glue" between accounts and contacts. However Sugar allows multiple entries in this table for a contact.

How do you specify the employer or 'primary' account for a contact?

Thanks,
Kirk Bocek

[dpatech]

Sugar allows multiple contacts to be related to a single account, but only one account per contact. Therefore, given any single contact, you can use the accounts_contacts table to find the account. There is nothing in this table that indicates which is the primary contact for the account.

[kuske]

Do not forget to look only to records of accounts_contacts with column deleted = 0.

When you change the account of a contact (e.g. at merging of accounts) records with deleted = 1 can stay in database table accounts_contacts.

[eggsurplus]

On the contacts screen the account name that you see is the first record returned from accounts_contacts. So you can select top 1 or limit 1,1 if you need just the account that is displayed on the screen.

[kbocek]

Thank you eggsurplus, that was the information I was looking for. I thought it might be something like that but wanted confirmation. Something along the lines of

select * from accounts_contacts where contact_id='xxx' and 
   deleted=0 order by id asc limit 1

should give me what I want.