Using Query Builder

[Angelina.jolie]

Hi,

I want create a query using query builder. but i want to know how to do it. the query is : List all similar company names occurring more than once in the table.

for example: If table “entry” has table column “id” and column “company_name” and there are some 1000 rows in this table.

If i have record like

Ibm1

Ibm2

Chelsea

MicrosoftA

MicrosoftB

Like this i have 1000 rows. only last few letters will be different. i want a query to list:

Ibm1

Ibm2

MicrosoftA

MicrosoftB.

Please help. I would appreciate an immediate positive reply from you. Hope i can get the query through QB. Or if you can tell the query for this i can use Sqlyog frequently and decide about buying it.

Thank you,

Angel

[peterlaursen]

I understand the query should be:

Code:

SELECT company_name FROM entry GROUP BY company_name HAVING count(company_name) > 1;

or (if you want the #of times it occurs displayed and and sorted by that:)

Code:

SELECT company_name, count(company_name) FROM entry GROUP BY company_name HAVING count(company_name) > 1 ORDER BY count(company_name) ;

Can you confirm that that is what you want (try execute them and see the result)? If it is this you want we will show with screenshots how to do in Query Builder to generate those..

[Chirantan Thakkar]

Hi,

Here are the screen-shots..

Please check it..

[Angelina.jolie]

I appreciate you taking time to reply. There is a small issue in it. Your query returns the names of same company present in database, but i also want it to return similar companies.

Example:

IbmA

123 IbmB

Swiss bankA

Swiss bank InternationalB

One is similar to another, so it should list all similar names.

Thanks for your help. And sqlyog is a great product to use.

Angel

[peterlaursen]

I am not sure I understand. Please confirm my understanding:

1) You have “IBM” with 5 records, “IBMA” with 3 records and “IBMB” with 1 record.

2) and you want the listing like this

Code:

IBM 5 {more details here}

IBMA 3 {more details here}

IBMB 1 {more details here}

.. and not just

Code:

IBM 5 {more details here}

IBMA 3 {more details here}

Correctly understood? If so it is a non-trivial query. It will probably require some table scans and string handling routines to solve it but undoubtedly can be solved in different ways.

Anyway can you tell if the “suffix” (“A”, “B” etc) are always a single character only? Will they always be letters? In what range – Only “A” and “B” – or “A” to “Z”

Frankly I think the database/table and the use of it could have been better designed (I could suggest adding a column to the table where to store the “A”, “B” etc. suffixes). And how would you handle the genuine case where there was a company names “Bast” and “Basta”?

[peterlaursen]

Also do you have a *fixed set* of company names?

[Author]

Posts