CODEWITHSUNDEEP
mysql
Soth
Soth

Reputation: 3045

MySQL Variable, GROUP_CONCAT not working

I have an issue where the variable doesn't equal the output. A pet has one owner and this tries to see if that owner has other pets and group them together. 

SELECT
o.id,
o.last_name,
@dog_names := GROUP_CONCAT(concat(p2.name,' (',REPLACE(REPLACE(p.type,'Big Dog','BD'),'Small Dog','LD'),')') SEPARATOR ', ') as dog_names,
@dog_names as dog_variable,
concat(o.last_name,' - ',@dog_names) as label
FROM pets p
LEFT JOIN owners o ON p.owner_id = o.id
LEFT JOIN pets p2 ON p.owner_id = p2.owner_id
WHERE p.name = 'wolfie'
GROUP BY o.id

The output is as follows:

id   | last_name | dog_names               | dog_variable | label
1647 | Reeve     | Wolfie (BD), Ember (BD) | Wolfie (BD)  | Reeve - Wolfie (BD)

Why does the dog_variable not match dog_names?

Upvotes: 1

Views: 1360

Answers (2)

medina
medina

Reputation: 8159

SELECT 
    o.id, 
    o.last_name, 
    @dog_names := GROUP_CONCAT(concat(p2.name,' (',REPLACE(REPLACE(p.type,'Big Dog','BD'),'Small Dog','LD'),')') SEPARATOR ', '),
    @dog_names as dog_names, 
    @dog_names as dog_variable, 
    concat(o.last_name,' - ',@dog_names) as label
FROM pets p
LEFT JOIN owners o ON p.owner_id = o.id
LEFT JOIN pets p2 ON p.owner_id = p2.owner_id
WHERE p.name = 'wolfie'
GROUP BY o.id

Upvotes: 0

gbn
gbn

Reputation: 432261

They are calculated at the same level in no particular order. This is how SQL works: it is declarative not procedural and no evaluation order can be assumed at the same level in any query.

In other words, @dog_names as dog_variable will use an older value of @dog_names because if has not been re-evaluated for this iteration

If you want it to be the sane, then repeat the expression or use a nested query

SELECT
   id, last_name,
   @dog_names := dog_names,
   @dog_names as dog_variable,
   concat(last_name,' - ', @dog_names) as label
FROM
    (
    SELECT
      o.id,
      o.last_name,
      GROUP_CONCAT(concat(p2.name,' (',REPLACE(REPLACE(p.type,'Big Dog','BD'),'Small Dog','LD'),')') SEPARATOR ', ') as dog_names
    FROM pets p
    LEFT JOIN owners o ON p.owner_id = o.id
    LEFT JOIN pets p2 ON p.owner_id = p2.owner_id
    WHERE p.name = 'wolfie'
    GROUP BY o.id, o.last_name
    ) x

Saying that, why use variables with GROUP_CONCAT?

Upvotes: 2

Related Questions