[Vtigercrm-developers] MySQL select statement

[Joe Bordes]

You can use DISTINCT in the columns clause and also try with EXISTS, 
something like:

select * from vtiger_accounts where exists (select invoiceid from 
vtiger_invoice where vtiger_accounts.accountid=vtiger_invoice.accountid)

Joe
TSolucio


On 18/12/12 16:17, John Crisp wrote:
> 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
> _______________________________________________
> http://www.vtiger.com/