SQL Help

**ftarara** · 2012-01-09, 03:57 PM

Hello,

I am looking for some help with a SQL statement that will allow me to select all contacts tied to an account of type "Customer" Shouldn't this be functionality that you can do from within Sugar? It would seem that you would be able to filter and export information like this, but I can't seem to figure it out. So I guess I have to go straight for the data.

Fabrizio

**jmertic** · 2012-01-10, 04:52 AM

Originally Posted by ftarara

Hello,

I am looking for some help with a SQL statement that will allow me to select all contacts tied to an account of type "Customer" Shouldn't this be functionality that you can do from within Sugar? It would seem that you would be able to filter and export information like this, but I can't seem to figure it out. So I guess I have to go straight for the data.

Fabrizio

Where are you trying to do this at? Is this from Web Services?

**SagarCRM** · 2012-01-10, 05:01 AM

do you want filter records while exporting it????
which sugar version you are using???

**rafael.q.g@hotmail.com** · 2012-01-10, 11:33 AM

Whether do you need to write the SQL over sugar database the code could be this one:

SELECT distinct(c.id),c.first_name,c.last_name FROM accounts a, contacts c,accounts_contacts a_c where c.deleted=0 and a_c.deleted=0 and a.deleted=0 and a_c.contact_id=c.id and a_c.account_id=a.id

**ftarara** · 2012-01-18, 11:55 AM

Sorry for the delayed response, I think I need to setup notifications from this forum.

We are currently using SugarCRM CE 6.3.1. I am needing this SQL to export records for our current email marketing system. So I need to export the data and import it into that system today. I do know that this is built into Sugar, but haven't made the transition yet!?!?!

Thanks for the help, I will try this SQL but it seems to be missing what I need. From my understanding I need to connect from Accounts, to Contacts, then the email relationship, then finally the email table.

Thanks,
Fabrizio

**ftarara** · 2012-01-18, 12:54 PM

Sorry for the delayed response. I am looking to just export contact and email addresses for another system to do emails from. I know that Sugar has this functionality, but we are still using our old system for the time being. So I am going to just use phpMyAdmin which was loaded with my stack to export the data for the time being. I think this SQL is a start, but my understanding is that I will need to link the account, contact, email relationship, and email table to get this accomplished. I have seen some SQL, but I don't think they are working completely.

Let me know if you need any more details, but I want to get a list of contacts and emails based on account_type at the Account level.

Thanks Fabrizio

**rafael.q.g@hotmail.com** · 2012-01-18, 03:35 PM

Maybe you need just to execute this sql using some MYSQL administration tool, and then export the result set in desired format. Most of MYSQL admin tools supports that exportation based in a query result set.

**ftarara** · 2012-01-25, 11:17 AM

Yes, that is what I am doing. This issue I am having is the SQL. I need some help with a rock solid query that will get me the results I need. I need all contact email addresses that are tied to a particular account type like "Customer". Does that make sense?

Fabrizio