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

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 a lot in building the query to get those results.

Andrew

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

Steven,

Since this isn't a PHP-specific question, you'll probably receive
better responses on either the MySQL list (you didn't mention which
database system you're using, but I'll blindly and ignorantly assume
that's it), or at least the PHP-DB list. I'm CC'ing both of those for
you.

--
</Daniel P. Brown>
Ask me about:
Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo.,
and shared hosting starting @ $2.50/mo.
Unmanaged, managed, and fully-managed!

On Tue, Apr 8, 2008 at 7:28 AM, Steven Macintyre
<steven@steven.macintyre.name> wrote:
> I have the following SQL statement;

.... and this relates to PHP how?

> SELECT count( salesID ) AS count, branch_name, company_name, branch.branchID

That doesn't make sense. You're selecting a group function (COUNT)
along with other columns which you're not grouping on. It might
if you told us what you were trying to accomplish with the query.

But you'd be better off asking on a SQL list instead of a PHP one.


> 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.

So why are you joining in the first place?

--
Mark J. Reed <markjreed@mail.com>

Steven Macintyre 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

I know that your DB might be able to figure out what is and is not ambiguous,
but, since we don't have the table structure to look at and compare. How about
using table.column naming for us.

Doing a desc `table_name` for us on each table would also.

>
> 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
>
>


--
Jim Lucas

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare