LS

dbIsam 3.3.

We copied from several companies their customer tables (same structure) to
one table with an extra field that holds the company. How do I query this
table so that the result is only customers that belong to more then one
company? Postal code and SurName are the identifiers.

Regards, Harry

Hi Harry,

Something like ..

SELECT Surname, Company FROM Customer WHERE COUNT(Surname) > 1 GROUP BY
Company, Surname;

--
Best regards

Steve

"Harry de Boer" <harry@staaf.nl> wrote in message
news:4C7517B7-7918-4D7C-8DC6-3D52B47FC419@news.elevatesoft.com...
> LS
>
> dbIsam 3.3.
>
> We copied from several companies their customer tables (same structure) to
> one table with an extra field that holds the company. How do I query this
> table so that the result is only customers that belong to more then one
> company? Postal code and SurName are the identifiers.
>
> Regards, Harry
>
>

Steve,

Alas: this is -for dbIsam 3.3- not a correct syntax.

Regards, Harry


"Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> schreef in bericht
news:5A81AE9B-FC3B-4E1B-8941-19F19EF5209E@news.elevatesoft.com...
> Hi Harry,
>
> Something like ..
>
> SELECT Surname, Company FROM Customer WHERE COUNT(Surname) > 1 GROUP BY
> Company, Surname;
>
> --
> Best regards
>
> Steve
>
> "Harry de Boer" <harry@staaf.nl> wrote in message
> news:4C7517B7-7918-4D7C-8DC6-3D52B47FC419@news.elevatesoft.com...
> > LS
> >
> > dbIsam 3.3.
> >
> > We copied from several companies their customer tables (same structure)
to
> > one table with an extra field that holds the company. How do I query
this
> > table so that the result is only customers that belong to more then one
> > company? Postal code and SurName are the identifiers.
> >
> > Regards, Harry
> >
> >
>
>

Hello Harry,

> Alas: this is -for dbIsam 3.3- not a correct syntax.
It would probably be a good idea for you to then post your question into
dbisam.sql to avoid confusion.

Off the top of my head, I think that this should work:

SELECT Surname, Company, COUNT(Surname) AS CustomeCount FROM Customer GROUP
BY Company,Name HAVING CustomerCount > 1

Regards
Trevor Keegan

Trevor,

AFAICS this only works if the same company name for a customer occurs more
then once. What I need is a list of customers that belongs to more then one
company.

Regards, Harry


"Trevor Keegan" <tkeegan@ealink.com> schreef in bericht
news:9C5ED5EF-DA73-4E70-9661-01E13DE46092@news.elevatesoft.com...
> Hello Harry,
>
> > Alas: this is -for dbIsam 3.3- not a correct syntax.
> It would probably be a good idea for you to then post your question into
> dbisam.sql to avoid confusion.
>
> Off the top of my head, I think that this should work:
>
> SELECT Surname, Company, COUNT(Surname) AS CustomeCount FROM Customer
GROUP
> BY Company,Name HAVING CustomerCount > 1
>
> Regards
> Trevor Keegan
>
>

Hello Harry,

Then it should work if you change the query to:

SELECT Surname, Company, COUNT(Company) AS CompanyCount FROM Customer GROUP
BY Company,Surname HAVING CompanyCount > 1

Regards
Trevor Keegan

Trevor

I'm sorry but that seems to be the same. I tested it with a surname 'Groote'
that occurs two times with different companies in the table. It is not in
the resultlist of the query however. Records with more then one occurence
that have the same surname/company are. So this seems to be not the answer
(I must be honest that I too thought it would be the right query statement).

Regards, Harry


"Trevor Keegan" <tkeegan@ealink.com> schreef in bericht
news:D8726E34-B8A1-4880-BDB0-FAD74C4BD9B5@news.elevatesoft.com...
> Hello Harry,
>
> Then it should work if you change the query to:
>
> SELECT Surname, Company, COUNT(Company) AS CompanyCount FROM Customer
GROUP
> BY Company,Surname HAVING CompanyCount > 1
>
> Regards
> Trevor Keegan
>
>

Harry,

Assuming that both postcode and surname need to match and that you also want
to select those with duplicates in the same company
how about -

SELECT Surname, Postcode, COUNT(Surname) AS CompanyCount FROM Customer GROUP
BY Surname,Postcode HAVING CompanyCount > 1

John


"Harry de Boer" <harry@staaf.nl> wrote in message
news:4C7517B7-7918-4D7C-8DC6-3D52B47FC419@news.elevatesoft.com...
> LS
>
> dbIsam 3.3.
>
> We copied from several companies their customer tables (same structure) to
> one table with an extra field that holds the company. How do I query this
> table so that the result is only customers that belong to more then one
> company? Postal code and SurName are the identifiers.
>
> Regards, Harry
>
>

John,

Both postcode and surname need to match, but the company can differ. What I
need is a list of customers that belongs to more then one company.

Regards, Harry



"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> schreef in
bericht news:3D956517-E38C-45C2-BA85-A356B3355172@news.elevatesoft.com...
> Harry,
>
> Assuming that both postcode and surname need to match and that you also
want
> to select those with duplicates in the same company
> how about -
>
> SELECT Surname, Postcode, COUNT(Surname) AS CompanyCount FROM Customer
GROUP
> BY Surname,Postcode HAVING CompanyCount > 1
>
> John
>
>
> "Harry de Boer" <harry@staaf.nl> wrote in message
> news:4C7517B7-7918-4D7C-8DC6-3D52B47FC419@news.elevatesoft.com...
> > LS
> >
> > dbIsam 3.3.
> >
> > We copied from several companies their customer tables (same structure)
to
> > one table with an extra field that holds the company. How do I query
this
> > table so that the result is only customers that belong to more then one
> > company? Postal code and SurName are the identifiers.
> >
> > Regards, Harry
> >
> >
>
>

Harry,

<< dbIsam 3.3. >>

Wrong newsgroup.

<< We copied from several companies their customer tables (same structure)
to one table with an extra field that holds the company. How do I query this
table so that the result is only customers that belong to more then one
company? Postal code and SurName are the identifiers. >>

What John Hay said should work, provided that the Company is unique for each
row.

--
Tim Young
Elevate Software
www.elevatesoft.com