2

I'm trying to make a MySQL query that uses different group_concat with data from the same LEFT JOIN. An example tables could be:

Cars table carid | license_plate

Tires table (means used tires) carid | model | width

From these tables i want to obtain a list of tire models classified by their width, with the idea that there are only two different possible widths and I'm loading it from a car card page.

 SELECT name, 
 if(width=205,group_concat(model ORDER BY model ASC),'') as width_205,
 if(width=225,group_concat(model ORDER BY model ASC),'') as width_225,
 FROM cars
 LEFT  JOIN tires ON cars.carid=tires.carid
 WHERE carid='10'

I hope that my explanation is clear. This sentence doesn't work properly, and I don't know if it's because of a bad syntax or that I simply can't use group_concat this way.

Well, thanks for reading and I'll be waiting for your answers. Thanks in advance!

Jonnix
  • 4,121
  • 1
  • 30
  • 31
xavi
  • 21
  • 1

2 Answers2

0

Try

SELECT name, 
  GROUP_CONCAT(CASE width WHEN 205 THEN model ELSE NULL END CASE AS model ORDER BY model) as width_205,
  GROUP_CONCAT(CASE width WHEN 225 THEN model ELSE NULL END CASE AS model ORDER BY model) as width_225,
FROM cars
LEFT  JOIN tires ON cars.carid=tires.carid
WHERE carid='10'
GROUP BY name

to get what you want. I don't have MySQL handy to test. You may have to fool with (or even give up) the internal ORDER BY.

Alternatively, you can get back two rows (and not have to change your SQL if a third size is ever added) with:

SELECT name, width, GROUP_CONCAT(model ORDER BY model) AS Models
FROM cars
LEFT  JOIN tires ON cars.carid=tires.carid
WHERE carid='10'
GROUP BY name, width
Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
0

Both solutions are great! Maybe the second is more elegant and finally it's the one I'll use, but I'll take note also from the first because it's more concrete and can be better for some situations.

For the first method to work I had to delete "CASE AS model" after the "END" to prevent MySQL errors:

SELECT name, GROUP_CONCAT(CASE width WHEN 205 THEN model ELSE NULL END ORDER BY model) as width_205, GROUP_CONCAT(CASE width WHEN 225 THEN model ELSE NULL END ORDER BY model) as width_225, FROM cars LEFT JOIN tires ON cars.carid=tires.carid WHERE carid='10' GROUP BY name

I also have added a DISTINCT before the CASE inside group_concat, to avoid repetitions.

For the second method, I only have added an "ORDER BY width ASC" at the end of the sentence. For anyone else that may need this method, don't forget that mysql_fetch_(whatever) already takes the first result that contains the first case of the group_concat.

Thanks a lot! CU

xavi
  • 1