[Vtigercrm-developers] MySQL select statement

[Adam Heinz]

I wouldn't use EXISTS in this case, compare the query plans:

mysql> explain extended select * from vtiger_account where exists (select
invoiceid from vtiger_invoice where
vtiger_account.accountid=vtiger_invoice.accountid);
+----+--------------------+----------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table          | type | possible_keys | key  |
key_len | ref  | rows | filtered | Extra       |
+----+--------------------+----------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | vtiger_account | ALL  | NULL          | NULL |
NULL    | NULL | 6170 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | vtiger_invoice | ALL  | NULL          | NULL |
NULL    | NULL |   10 |   100.00 | Using where |
+----+--------------------+----------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> explain extended select * from vtiger_account inner join
vtiger_invoice using (accountid);
+----+-------------+----------------+--------+---------------+---------+---------+---------------------------------+------+----------+-------+
| id | select_type | table          | type   | possible_keys | key     |
key_len | ref                             | rows | filtered | Extra |
+----+-------------+----------------+--------+---------------+---------+---------+---------------------------------+------+----------+-------+
|  1 | SIMPLE      | vtiger_invoice | ALL    | NULL          | NULL    |
NULL    | NULL                            |   10 |   100.00 |       |
|  1 | SIMPLE      | vtiger_account | eq_ref | PRIMARY       | PRIMARY | 4
      | vtiger540.vtiger_invoice.accountid |    1 |   100.00 |       |
+----+-------------+----------------+--------+---------------+---------+---------+---------------------------------+------+----------+-------+



On Tue, Dec 18, 2012 at 10:39 AM, Joe Bordes <joe at tsolucio.com> wrote:

> 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/
>
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20121218/807e3509/attachment.html