[Vtigercrm-developers] MySQL select statement

[John Crisp]

Hi,

I'm trying to run a select statement to get a simple list of all
Accounts that have been invoiced this year. MySQL is definitely not my
strong point :-(

I have got this far :

SELECT
`vtiger_account`.`accountid`,
`vtiger_account`.`accountname`,
`vtiger_account`.`account_no`,
`vtiger_accountscf`.`cf_592`

FROM `crm`.`vtiger_crmentity` INNER JOIN `crm`.`vtiger_account` INNER
JOIN `crm`.`vtiger_accountscf` INNER JOIN `crm`.`vtiger_invoice`

WHERE `vtiger_crmentity`.`crmid` = `vtiger_account`.`accountid`
AND `vtiger_crmentity`.`crmid` = `vtiger_accountscf`.`accountid`
AND `vtiger_crmentity`.`crmid` = `vtiger_invoice`.`accountid`
AND `vtiger_crmentity`.`deleted` = 0
AND `vtiger_invoice`.`invoicedate` > 2012-01-01


But if an Account has more than one Invoice, the result rows are
obviously duplicated.

I am pretty sure it is to do with the JOINs but I'm blowed if I can
figure it out.

The Logic is :

"Show all ACCOUNTS that have been invoiced at least once this year"

I should get a rough list that looks like :

Acct ID | Acct Name | Acct Number | Custom Field

Any help would be gratefully received !!!!

B. Rgds
John