Re: joins issues again
| From: | Andrew Ballard | Date: | Tue, 08 Apr 2008 13:41:42 +0000 |
| Subject: | Re: joins issues again | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-272703@lists.php.net to get a copy of this message | ||
On Tue, Apr 8, 2008 at 7:28 AM, Steven Macintyre
<steven@steven.macintyre.name> wrote:
> Hi all,
>
> I have the following SQL statement;
>
> SELECT count( salesID ) AS count, branch_name, company_name, branch.branchID
> FROM sales
> LEFT JOIN IGuser ON sales.IGuid = IGuser.IGuid
> LEFT JOIN branch ON IGuser.branchID = branch.branchID
> LEFT JOIN company ON branch.companyID = '{$companyID}'
> WHERE maincompanyid = '{$mcid}'
> GROUP BY branch.branchID
> ORDER BY branch_name ASC
>
> However, i do not want those join records to be appended, only to return the count of records
> from sales.
>
> Can someone assist me with this? I have tried differance variants of joins and none of the
> results are correct.
>
> Sales tbl doesnt have the companyID, nor does IGuser
>
> Regards,
>
> Steven
A couple things:
1) Are you looking for COUNT(DISTINCT salesID) rather than COUNT(salesID)?
2) Change your group clause to this: GROUP BY branch.branchID,
branch_name, company name
MySQL is pretty forgiving and will let you include fields in the
SELECT that are neither aggregated nor grouped, but it's bad practice.
Beyond that, a clearer explanation of what you expect to see in the
results would help a lot in building the query to get those results.
Andrew