CODEWITHSUNDEEP
mysqlif-statementgroup-concat
peace_love
peace_love

Reputation: 6471

How can I use ELSE IF inside GROUP_CONCAT?

I have an IF statement inside a mySQL query and it is working well:

 GROUP_CONCAT(DISTINCT CONCAT("animal:",if(animal.name="monkey","fred",""),",color: ",animal.color) SEPARATOR " <br>") AS animals

I want now add an ELSEIF:

  GROUP_CONCAT(DISTINCT CONCAT("animal:",if(animal.name="monkey","fred","")elseif(animal.name="cat","jane",""),",color: ",animal.color) SEPARATOR " <br>") AS animals

But I get an ERROR message:

Syntax error or access violation: 1064 You have an error in your SQL syntax

Upvotes: 1

Views: 629

Answers (2)

slaakso
slaakso

Reputation: 9080

The "if elseif endif" is a control statement whereas the if() is a function in MySQL. While they share the same name they are different things.

If you have multiple options, you can use CASE instead of IF.

SELECT 
    GROUP_CONCAT(DISTINCT CONCAT("animal:",
      CASE animal.name
        WHEN "monkey" THEN "fred"
        WHEN "cat" THEN "jane"
        ELSE "other"
      END,
      ",color: ",animal.color) SEPARATOR " <br>"
    ) AS animals
FROM (
  SELECT 'monkey' AS 'name', 'brown' AS 'color'
  UNION
  SELECT 'cat', 'white'
  UNION
  SELECT 'dog', 'black'
  ) AS animal

Upvotes: 3

guigoz
guigoz

Reputation: 704

you have to add a if in the else part of your if

group_concat(distinct concat("animal:",if(animal.name="monkey","fred",if(animal.name="cat","jane","")),",color: ",animal.color) separator " <br>") as animals

the case syntax is almost the same

group_concat(distinct concat("animal:",case animal.name when "monkey" then "fred" when "cat" then "jane" case "dog" then "donald" else "",",color: ",animal.color) separator " <br>") as animals

Upvotes: 2

Related Questions