0

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

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Gerardo García
  • 11
  • 6

3 Answers3

0

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
  • 28,155
  • 10
  • 49
  • 57
  • It sounds logical, not work for me, the MySQL Workbench thows this: "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" – Gerardo García Nov 28 '18 at 15:37
  • @GerardoGarcía it seems that you are not running my query yet. Because if you check closely, I dont have `GROUP BY` in my query – Madhur Bhaiya Nov 28 '18 at 15:38
  • Yes you're right, Now I tested your query, it doesnt thow errors, but have the same result: https://i.ibb.co/NY3jQLp/Captura-de-pantalla-2018-11-28-a-la-s-10-11-29.png – Gerardo García Nov 28 '18 at 16:14
  • @GerardoGarcía check carefully there are no same results. Some have additional spaces or `.` between them – Madhur Bhaiya Nov 28 '18 at 16:19
  • yes Im Ok with you. The Idea is Only shows One Dealer Name per Dealer ID, as you can see on the picture, and I'm agree with you some have additional spaces or . between them. For example on "ACO960619AY9", it throws two results: "ACEROS Y CORRUGADOS SA DE CV" and "ACEROS Y CORRUGADOS SA. DE CV." But Only need to get One result – Gerardo García Nov 28 '18 at 16:44
  • @GerardoGarcía check the updated answer. Changed to a GROUP BY instead – Madhur Bhaiya Nov 28 '18 at 16:47
  • I saw your updated answer and tried, but it Throws me this 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 " – Gerardo García Nov 28 '18 at 17:32
  • @GerardoGarcía my query does not have `facturacion.facturas_recibidas.Emisor_Razon_Social` ; where is this coming from ? I think you are modifying my query and adding some more parts to it. It wont work just like that with `GROUP BY`. You can read this for understanding: https://stackoverflow.com/a/34115425/2469308 – Madhur Bhaiya Nov 28 '18 at 17:43
0

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.

Robertas Murza
  • 46
  • 1
  • 3
0

@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.

Gerardo García
  • 11
  • 6