I wouldn't use EXISTS in this case, compare the query plans:<div><br></div><div><div>mysql> explain extended select * from vtiger_account where exists (select invoiceid from vtiger_invoice where vtiger_account.accountid=vtiger_invoice.accountid);</div>
<div>+----+--------------------+----------------+------+---------------+------+---------+------+------+----------+-------------+</div><div>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |</div>
<div>+----+--------------------+----------------+------+---------------+------+---------+------+------+----------+-------------+</div><div>| 1 | PRIMARY | vtiger_account | ALL | NULL | NULL | NULL | NULL | 6170 | 100.00 | Using where |</div>
<div>| 2 | DEPENDENT SUBQUERY | vtiger_invoice | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |</div><div>+----+--------------------+----------------+------+---------------+------+---------+------+------+----------+-------------+</div>
<div>2 rows in set, 2 warnings (0.00 sec)</div><div><br></div><div>mysql> explain extended select * from vtiger_account inner join vtiger_invoice using (accountid);</div><div>+----+-------------+----------------+--------+---------------+---------+---------+---------------------------------+------+----------+-------+</div>
<div>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |</div><div>+----+-------------+----------------+--------+---------------+---------+---------+---------------------------------+------+----------+-------+</div>
<div>| 1 | SIMPLE | vtiger_invoice | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | |</div><div>| 1 | SIMPLE | vtiger_account | eq_ref | PRIMARY | PRIMARY | 4 | vtiger540.vtiger_invoice.accountid | 1 | 100.00 | |</div>
<div>+----+-------------+----------------+--------+---------------+---------+---------+---------------------------------+------+----------+-------+</div></div><div><br></div><div class="gmail_extra"><br><br><div class="gmail_quote">
On Tue, Dec 18, 2012 at 10:39 AM, Joe Bordes <span dir="ltr"><<a href="mailto:joe@tsolucio.com" target="_blank">joe@tsolucio.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
You can use DISTINCT in the columns clause and also try with EXISTS,<br>
something like:<br>
<br>
select * from vtiger_accounts where exists (select invoiceid from<br>
vtiger_invoice where vtiger_accounts.accountid=vtiger_invoice.accountid)<br>
<br>
Joe<br>
TSolucio<br>
<div><div class="h5"><br>
<br>
On 18/12/12 16:17, John Crisp wrote:<br>
> Hi,<br>
><br>
> I'm trying to run a select statement to get a simple list of all<br>
> Accounts that have been invoiced this year. MySQL is definitely not my<br>
> strong point :-(<br>
><br>
> I have got this far :<br>
><br>
> SELECT<br>
> `vtiger_account`.`accountid`,<br>
> `vtiger_account`.`accountname`,<br>
> `vtiger_account`.`account_no`,<br>
> `vtiger_accountscf`.`cf_592`<br>
><br>
> FROM `crm`.`vtiger_crmentity` INNER JOIN `crm`.`vtiger_account` INNER<br>
> JOIN `crm`.`vtiger_accountscf` INNER JOIN `crm`.`vtiger_invoice`<br>
><br>
> WHERE `vtiger_crmentity`.`crmid` = `vtiger_account`.`accountid`<br>
> AND `vtiger_crmentity`.`crmid` = `vtiger_accountscf`.`accountid`<br>
> AND `vtiger_crmentity`.`crmid` = `vtiger_invoice`.`accountid`<br>
> AND `vtiger_crmentity`.`deleted` = 0<br>
> AND `vtiger_invoice`.`invoicedate` > 2012-01-01<br>
><br>
><br>
> But if an Account has more than one Invoice, the result rows are<br>
> obviously duplicated.<br>
><br>
> I am pretty sure it is to do with the JOINs but I'm blowed if I can<br>
> figure it out.<br>
><br>
> The Logic is :<br>
><br>
> "Show all ACCOUNTS that have been invoiced at least once this year"<br>
><br>
> I should get a rough list that looks like :<br>
><br>
> Acct ID | Acct Name | Acct Number | Custom Field<br>
><br>
> Any help would be gratefully received !!!!<br>
><br>
> B. Rgds<br>
> John<br>
> _______________________________________________<br>
> <a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a><br>
<br>
</div></div>_______________________________________________<br>
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a><br>
</blockquote></div><br></div>