On 3 Sep, 15:14, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Mon, 03 Sep 2007 15:27:10 +0200, Dave
>
>
>
>
>
> <david.greenh...@praybourne.co.uk> wrote:
> > Not sure how to explain what it is I am after, so forgive me if the
> > subject does not help.
>
> > Basically
>
> > I have a two tables like so:
>
> > table1 (
> > id int(5) auto_increment primary key,
> > code char(11),
> > account_number char(11)
> > )
>
> > table2 (
> > company_id char(11) primary key,
> > company_name char(100)
> > )
>
> > I have created the following query:
> > $getcount = mysql_query("SELECT account_number,code, COUNT(*) FROM
> > table1
> > JOIN table2 ON table1.account_number = table2.company_id
> > GROUP BY code ORDER BY COUNT(*) ");
>
> Possibly (untested)
>
> SELECT
> table1.code,
> COUNT(DISTINCT table2.company_id) as 'amount',
> GROUP_CONCAT(DISTINCT table2.company_id SEPARATOR ', ' as 'companies'
> # or if you like GROUP_CONCAT(DISTINCT table2.company_name SEPARATOR ', '
> as 'companies'
> FROM table1
> LEFT JOIN table2
> ON table1.account_number = table2.company_id
> GROUP BY table1.code
> ORDER BY `amount`
> --
> Rik Wasmus- Hide quoted text -
>
> - Show quoted text -
Still untested but more likely to run:

SELECT
table1.code,
COUNT(DISTINCT table2.company_id) as `amount`,
GROUP_CONCAT(DISTINCT table2.company_id SEPARATOR ', ') as
`companies`
# or if you like GROUP_CONCAT(DISTINCT table2.company_name
SEPARATOR ', ')
as `companies`
FROM table1
LEFT JOIN table2
ON table1.account_number = table2.company_id
GROUP BY table1.code
ORDER BY `amount`