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

Question

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 class="col-lg-4 col-md-6" style="margin-bottom: 20px">
          <div class="card bg-black" style="text-align: center;">
               <div class="card-body">
                   <h3 class="card-text text-white"><?= $row['name']?></h3>
                   <p class="text-danger"><?= $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".

Not exactly I forgot to mention I'll edit it right away, that if its only one city it would show the name of the city which happened to be "City 1" or "City 2" — Nomi
– Nomi, Commented Feb 7, 2022 at 15:17
Is there a reason why you are not using PDO? With PDO you have really cool fetch modes that would definitely help you achieve what you want. — Dharman
– Dharman ♦, Commented Feb 7, 2022 at 15:19
You could also do something like this in SQL IF(COUNT(*) > 1, COUNT(*), Name) as cityname Is this what you are looking for? — Dharman
– Dharman ♦, Commented Feb 7, 2022 at 15:22

Dharman · Accepted Answer · 2022-02-07 16:28:51Z

1

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

edited Feb 7, 2022 at 16:28

answered Feb 7, 2022 at 15:26

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Nomi Over a year ago

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'nkursdb.city.city_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Dharman Over a year ago

@Nomi You can ignore it with ANY_VALUE()

Dharman Over a year ago

Relevant: stackoverflow.com/questions/37089347/…

Nomi Over a year ago

Nope I can't get it working

Nomi Over a year ago

can u edit your demonstration with the ANY_VALUE() part

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related