How to take one value from two columns with DISTINCT in mySQL?

General
1

Hi all!.

I have a database with these two columns:

  1. Razon Social
  2. RFC Emisor

Where:
RFC Emisor its an unique number that identifies every customer or company, eg. GARG87652TG9
So two customers or company can’t have the same RFC.

Razn Social: its the name of the Customer or company.

But many times customers they misspell his name, for example:

Razon Social RFC Emisor
PEREZ&SON’S Landscaping Co. Ltd --------- GARG87652TG9
PEREZ&SON’S Landscaping Co. Ltd --------- GARG87652TG9
PEREZ&SON’S Landscaping, Co. Ltd --------- GARG87652TG9
PEREZ&SON’S Landscaping, Co. L. t. d --------- GARG87652TG9
PEREZ&SON’S Landscaping, Co. LTD St. Infinite Loop --------- GARG87652TG9

In this database are a lot of customers with Name(Razon Social), and RFC Emisor.
But I only want to DISTINCT one customer using mySQL.

I mean I only want to make DISTINCT of RFC Emisor and choose only one name(RAZON SOCIAL) from the listed one.

I’m using this, but not does that I want:
“SELECT DISTINCT REPLACE((CONCAT(Emisor_Razon_Social,Emisor_RFC)), Emisor_RFC, ‘’) AS cc FROM Facturas_Recibidas”

Any suggestions?
Thanks

2

Why you don’t use the primary id?
Every record have primary key as id.

3

[quote=262216:@Loannis Kolliageorgas]Why you don’t use the primary id?
Every record have primary key as id.[/quote]
But its a very very large list of customers

  1. I can’t convert to unique the “RFC” cuz its a list of customers and items sold to us.
    But I use this list to only get a list of customers
4 
SELECT RFC,min(CUSTOMER) as CUSTOMER from <table> group by RFC

this will return ONE occurace of RFC, with ONE spelling of Customer

5

and if you want to know who has multiple spellings

SELECT RFC,COUNT(DISTINCT CUSTOMER) FROM TABLE GROUP BY RFC HAVING COUNT(DISTINCT CUSTOMER)>1
6

SELECT Emisor_Razon_Social , Emisor_RFC FROM Facturas_Recibidas Group By Emisor_RFC

SInce it has only 2 columns you could just do

Select * from Facturas_Recibidas Group By Emisor_RFC

7

[quote=262240:@damon pillinger]SELECT Emisor_Razon_Social , Emisor_RFC FROM Facturas_Recibidas Group By Emisor_RFC

SInce it has only 2 columns you could just do

Select * from Facturas_Recibidas Group By Emisor_RFC[/quote]

Actually no you can’t, as he said, the Customer name is subject to being mis-spelled, so you need code that will choose ONE of the mis-spellings …

8

The users shouldn’t be asked to enter both the Razon Social and the RFC Emisor. Your app should ask for one and fill out the other automatically. You’re giving your users too big a chance to mess things up.

9

Absolutely correct.
If they have data entry screens, give them a list of names, let them choose one, and use the unique ID that is related to it.
Only if the name is missing is there a case to allow them to enter a new name, at which time a new RFC Emisor is required.

Use Dave’s multiple spellings code to identify RFC Emisor values that have two or more descriptions. Delete all but one of the rows.

Since RFC Emisor is supposed to be a unique identifier for a customer, arguably the table is not correctly defined, because it allows duplicates, making the ‘unique’ ID anything but.
You should set the database to not allow a new row with an existing ID, which means it will never happen again.

10

Remember… he said “I HAVE A DATABASE”… he did not say, “I HAVE AN APP I WROTE THAT CREATES A DATABASE”

He may have no control over the data in the database, how it gets there, etc… his question was specificially how to get it OUT

11

Very good point.

Then I hope it is his database.

Using MIN() or MAX() will definitely get one value only, but if the table contains

PEREZ&SON’S Landscaping Co. Ltd --------- GARG87652TG9
Bills Gardening --------- GARG87652TG9
QWERTYUIOP--------- GARG87652TG9

The single output row could be less than useful. :slight_smile: