MYSQL SELECT statement inside CONCAT function

Question

I was trying to do something like this in my very big select statement, this code snippet is from the SELECT part of the query

CONCAT((SELECT alias.`date` FROM alias WHERE id IN(latest_id)),'<-',GROUP_CONCAT(
`alias`.`date` ORDER BY `alias`.`date` DESC SEPARATOR '<-')) AS "date_chain"

but I am getting NULL in "date_chain" column. If I only write this

GROUP_CONCAT(
`alias`.`date` 
ORDER BY `alias`.`date` DESC SEPARATOR '<-') AS "date_chain"

It works.

But I want to concat the latest date in the start of this chain.

adding full SQL

SELECT latest_id,CONCAT((SELECT alias.`date`FROM alias WHERE id IN (latest_id)),'<-',
GROUP_CONCAT(
  `alias`.`date` 
  ORDER BY `alias`.`date` DESC SEPARATOR '<-'
)) AS "date_chain" FROM alias WHERE latest_id IS NOT NULL GROUP BY latest_id;

Kindly can someone help me what is missing in my first syntax? Thank you

If you need further help you need to provide full query. Also, best way of debugging queries like that is to run simple SELECT statements separately to see "where the NULL happens" :) — Kamil Gosciminski
– Kamil Gosciminski, Commented Apr 15, 2015 at 21:02
Please put this in your question - let's respect the reader. It's unreadable this way. — Kamil Gosciminski
– Kamil Gosciminski, Commented Apr 15, 2015 at 21:07
Your query should be rewritten - it's not efficient. But anyways, run SELECT date FROM alias WHERE id = latest_id and post number of rows that you get from this query. — Kamil Gosciminski
– Kamil Gosciminski, Commented Apr 15, 2015 at 21:18

Kamil Gosciminski · Accepted Answer · 2015-04-15 22:16:16Z

When any value that's an arugment for CONCAT() function is NULL - the output of function evaluates to NULL. See manual for reference.

CONCAT() returns NULL if any argument is NULL.

Try CONCAT_WS() where you can also specify a separator.

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

However, don't put NULL as separator - that would cause the result to yield NULL.

Edit after comments

We established that the reason for this was improper use of outer query column latest_id as a feed for inner SELECT.

  SELECT alias.`date`FROM alias WHERE id IN (latest_id)

was just comparing each id to latest_id from the same row, while the desired result was to compare it to column from outer SELECT block.

Query after changes should be

SELECT 
  latest_id,
  CONCAT(
    (SELECT alias.`date`FROM alias WHERE id IN (o.latest_id)),
    '<-',
    GROUP_CONCAT(
      `alias`.`date` ORDER BY `alias`.`date` DESC SEPARATOR '<-'
    )
  ) 
  AS "date_chain" 
FROM alias o
WHERE latest_id IS NOT NULL
GROUP BY latest_id;

Though your query gives you what you want it is not the best way to achieve this result. You should in most cases avoid using CONCAT() because of NULL values and your SELECT inside an outer SELECT block as a column feed would make the query run slow (it must compute values for every row).

Please consider the following code as better practice to get the same result

SELECT
  foo.latest_id,
  CONCAT_WS('<-', a.date, foo.group_concat) AS date_chain
FROM(
  SELECT  
    latest_id, 
    GROUP_CONCAT(date ORDER BY date DESC SEPARATOR '<-') AS group_concat
  FROM alias
  WHERE latest_id IS NOT NULL
  GROUP BY latest_id
) foo
LEFT JOIN alias a ON
  foo.latest_id = a.id

Collectives™ on Stack Overflow

MYSQL SELECT statement inside CONCAT function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related