Btukfyl

I have a MySQL Table, and a table where I do the next Query:
Select Diferent Customer_Name in a Table where Customer_ID=CED130828MJ8.

The goal of that query is that only get a List on Customer_Name and Customer_ID.

Sometimes certains Customer_ID lacks of Customer_Name, (yep, I know about Unique KEY, but, thats not the ISSUE). So, cuz of this I do a CONCAT, IF Customer_Name is Empty, then Customer_Name = Customer_ID

Obiously ID_USER will change.
Thats the Query that I do:

SELECT DISTINCT(

        IF(Dealer_Name<>'',

            CONCAT(Dealer_Name, '*',Dealer_ID),

            CONCAT(Dealer_ID, '*',Dealer_ID)

        )

      ) AS cc FROM received_invoices 

WHERE Customer_ID='CED130828MJ8' 

GROUP BY (cc) 

ORDER BY cc ASC";

And thats how It looks the result of that QUERY:

"BEST BUY LTD CO*BTB052YU96"

And Almost works, I mean almost, because It only choose one Customer_ID of the table, but If a Customer_ID has two ore more records, it Will print, two records.

Let me show you:

"GERARDO GARCIA RIVERA*GARG870805726"

"GERARDO GARSIA RIBERA*GARG870805726"

"JERARDO GARZIA RIVERA*GARG870805726"

"JUAN ANTONIO MUÑOZ*MUGJ540314TV4"

"GUAN ANTONIO MUNIOZ*MUGJ540314TV4"

"JUAN ANTOÑO MUÑIOS*MUGJ540314TV4"

"JUAN HANTOIO MUÑOC*MUGJ540314TV4"

BUT THAT I really Want instead of that is only Get 1 Record per Customer_ID, like:

"GERARDO GARCIA RIVERA*GARG870805726"

"JUAN ANTONIO MUÑOZ*MUGJ540314TV4"

What Do I missing?, what did you suggest?

Thanks in Advance

SQL sees concatenated strings as different:

"GERARDO GARCIA RIVERA*GARG870805726"
"GERARDO GARSIA RIBERA*GARG870805726"
"JERARDO GARZIA RIVERA*GARG870805726"


If you only want to see one entry per Customer_ID, you will also get one name, not all of them. If it does not matter which one will be displayed, then use min() on it:

select
if(Dealer_Name<>'',
concat(min(Dealer_Name), '*',Dealer_ID),
concat(Dealer_ID, '*',Dealer_ID)
)
as cc from received_invoices
where Customer_ID='CED130828MJ8'
group by Dealer_ID
order by cc asc";


Note, that grouping is now done by Dealer_ID, not full concatenated string.

Few observations:

• 
  DISTINCT is not a function. So you don't need to use parentheses around it.


• 
  DISTINCT with GROUP BY on same column/alias is not required.

Try the following instead:

SELECT 

  CONCAT(IF(MAX(Dealer_Name) <> '', MAX(Dealer_Name), Dealer_ID), 

         '*', 

         Dealer_ID

        ) AS cc 

FROM received_invoices 

WHERE Customer_ID = 'CED130828MJ8' 

GROUP BY Dealer_ID 

ORDER BY cc ASC

@Madhur Bhaiya.

Before All, Thanks for your Attention, Your great, As you Said, that is the answer:

SELECT 

  CONCAT(IF(MAX(Dealer_Name) <> '', MAX(Dealer_Name), Dealer_ID), 

         '*', 

         Dealer_ID

        ) AS cc 

FROM received_invoices 

WHERE Customer_ID = 'CED130828MJ8' 

GROUP BY Dealer_ID 

ORDER BY cc ASC

In my particular case, might be not in other people, when I do that query it throws me that Error: Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'facturacion.facturas_recibidas.Emisor_Razon_Social' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So I google about it, and I found that when this kind of error happens, I must DISABLE-ONLY-FULL-GROUP-BY.

And in order to do that I need to modify my /etc/mysql/my.cnf Cuz I have Ubuntu Linux I do it at this way:

1. sudo nano /etc/mysql/my.cnf


2. Add this to the end of the file


3. [mysqld]
   sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 4. For Restart the MySQL server and apply the changes: "sudo service mysql restart" THIS WILL DISABLE ONLY_FULL_GROUP_BY for ALL users.

Then Do the Query Again, and Voila!!, The Query Works ok!!!!.

So now I need to Modify the my.cnf file after installed mysql on every platform of my customers (Windows/Linux/MAC).

In order to avoid that Errors.