Group Data with Preference-MYSQL

Navigation
2

I'm trying to group rows equal, but based on a value. I know it is possible through sub-query , however as I am using inner join I could not 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:

DBstructure: Problem:

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

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

How can I group multiple equal items, preferably one field (in the case of Marks_id)?

    
asked by anonymous 23.12.2017 / 05:59

1 answer

1

The solution we found was to make a subquery using the INNER JOIN by filtering by the state id and grouping it by the tag_id field and then sorting the temporary table by marcas_id in the external query grouped by the id of the concessionaire and again we ordered by attribute marcas_id 

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 to saidmm for working on problem solving

    
23.12.2017 / 19:26
Laravel 5.5: WhereHas in relationship many to one Edit form Laravel