Grouping Data Preferably-MYSQL

Asked

Viewed 513 times

2

I’m trying to group equal lines, but based on a value. I know it’s possible through a sub-query, but how I’m using inner join I couldn’t apply.

 SELECT * FROM Concessionarias C inner join Concessionarias_Marcas CM on  
 C.id = CM.Concessionarias_id and C.Estados_id = 14 group by C.id order by 
 (Marcas_id = 31) DESC;

This Query returns the following information: inserir a descrição da imagem aqui

Structure of the BD : inserir a descrição da imagem aqui Problem:

When I put to group, it groups correctly, but wanted when grouping, to group the item that is with (Marks_id = 31).

Since I have 2 repeated lines, when grouping, he would choose the line where Marcas_id is equal to 31. In this case, it is choosing (Marks_id = 12)

As I could group several equal items , preferably by a field (in the case of Marks_id)?

mysql database query

  • You only posted the result of your query with group and Inner Join has no way to understand the question got confused, post the structure of the two tables. Maybe I can help!

  • Exactly what I want,show all dealerships,but show preferably the Dealerships that have the same customer model. The way it is now, it groups and orders, but when ordering, it orders wrong because the Marcas_id that he took is not the value of the client’s model,ai ends up affecting the order of the list.As I would solve with WHERE?

  • I’ve removed to look at some of my darlings here, already I answer again

  • Yes you want all records but ordered by the customer brand on the correct front ?

  • That, Exactly !

  • I went there to look at a darling that I have something similar, however it generates two distinct queries in my code to do. What I know and could help you is the ORDER BY FIELD command (Marcas_id, 31,21,6,12); which provides the sort but to use it would have to have another querie before picking the ids to use inside the command.

  • Could you show me the code? ,will solve my problem.

  • Let’s go continue this discussion in chat.

Show 3 more comments

1 answer

1


The solution we found was to do a subquery using the INNER JOIN filtering by state id and grouping by field marcas_id and then sorting the time table by marcas_id in external querie grouped by the dealer id and again sorted by the tag attribute 

SELECT * FROM (SELECT * FROM concessionarias C inner join 
concessionarias_marcas CM on 
C.id = CM.Concessionarias_id and C.Estados_id = 14 GROUP BY Marcas_id  order 
by (Marcas_id = 31) DESC) AS CCM GROUP BY id order by  (CCM.Marcas_id = 31) 
DESC;

Credits payable for collaboration in solving the problem

  • Thanks! If it wasn’t for You’d spend Christmas in front of the PC.

Browser other questions tagged mysql database query

You are not signed in. Login or sign up in order to post.