SQL joining different rows into one row and changing the output based on the values-CodePudding

I'd like to output two rows into one and change the output based on the values

SQL syntax:

SELECT * 
FROM course 
  LEFT OUTER JOIN located ON course.course_id = located.course_id 
  LEFT OUTER JOIN city ON city.city_id = located.city_id

PHP syntax:

  while ($row=$result->fetch_assoc()) { ?>
     <div  style="margin-bottom: 20px">
          <div  style="text-align: center;">
               <div >
                   <h3 ><?= $row['name']?></h3>
                   <p ><?= $row['city_name'];?></p>
               </div>
          </div>
     </div>
   <?php
   }
   ?>

The output:

How I'd like it to be:

But if it is only one city it would show the name of the city which happened to be "City 1" or "City 2".

CodePudding user response：

You can do it by grouping the results in SQL and then using a conditional select. For example:

SELECT 
  course.name, 
  IF(COUNT(*) > 1, COUNT(*), MIN(city_name)) as city_name
FROM course 
  LEFT OUTER JOIN located ON course.course_id = located.course_id 
  LEFT OUTER JOIN city ON city.city_id = located.city_id
GROUP BY course.course_id

In your case, maybe something like this would be more suitable

SELECT 
  IF(city_count > 1, CONCAT(city_count, ' cities'), city_name) as city_name
FROM course 
  LEFT OUTER JOIN 
    (SELECT course_id, COUNT(*) as city_count, MIN(city_name) as city_name
      FROM located  
        LEFT OUTER JOIN city ON city.city_id = located.city_id
      GROUP BY located.course_id
    ) cities ON course.course_id = cities.course_id
GROUP BY course.course_id