[Vtigercrm-developers] MySQL select statement

[John Crisp]

On 18/12/12 16:27, Adam Heinz wrote:
> And I forgot to mention SELECT DISTINCT
> 
> 
> On Tue, Dec 18, 2012 at 10:25 AM, Adam Heinz <amh at metricwise.net
> <mailto:amh at metricwise.net>> wrote:
> 
>     I don't generally prefix the database name unless I'm doing
>     multi-database joins.  Move your where logic up into the joins and
>     delete it from the where clause.
> 


Thanks !!!! I only did the prefix because of the environment I am using
to mess about with it.

Aaarrrgggghhhhhh !!!!!



Well at least I guessed it had something to do with the JOINS :-)

I *think* this has got me what I needed :

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

FROM `vtiger_crmentity`
INNER JOIN `vtiger_account` ON `vtiger_account`.`accountid` =
`vtiger_crmentity`.`crmid`
INNER JOIN `vtiger_accountscf` ON `vtiger_accountscf`.`accountid` =
`vtiger_account`.`accountid`
INNER JOIN `vtiger_invoice` ON `vtiger_invoice`.`accountid` =
`vtiger_account`.`accountid`
AND YEAR(`vtiger_invoice`.`invoicedate`) = 2012


Just need to physically tot up the rows but I think it has done the job.

Many thanks.

B. Rgds
John