Sql Query

SuiteCRM Forum - English Language Developer Collaboration
#1

Hello,

I am running a sql query to print some data from contacts table using id
i want to print also account name associated with the contact name . How can i do that. for ex i want to print contact name and account name for contact id 34

what will be the best query to select data from all the three tables

  1. accounts
  2. contacts
  3. accounts_contacts

as third table contain id for the both the table records.

#2

Hi,

Something like the following should do the trick:

SELECT contacts.id,contacts.first_name,contacts.last_name,accounts.id,accounts.name 
FROM contacts 
LEFT JOIN accounts_contacts ON (contacts.id = accounts_contacts.contact_id AND accounts_contacts.deleted = 0) 
LEFT JOIN accounts ON (accounts.id = accounts_contacts.account_id AND accounts.deleted = 0) 
WHERE contacts.deleted = 0;

You may wish to switch the LEFT JOINs to INNER JOINs if you wish to only show contacts that have an account. You can also add an extra clause to return the details for specific contacts (i.e. add AND contacts.id = 34).

Thanks,
Jim

1 Like