0

I have following query which contain group_concat and SQL_CALC_FOUND_ROWS together as I want result + total no. of records present in it.

SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(ca.id) 
FROM notification AS ca 
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 ' 
AND  ca.date!="" 
AND  ca.alert_msg_id 
NOT IN(26,27) AND ca.from_id != '777'

SELECT FOUND_ROWS();

But this does not return the desired result.

Please suggest.

  • 1
    Give us some DDL (SHOW CREATE TABLE notification) and some DML (INSERT INTO notification VALUES(...)). Give us the result you expect and your logic in deriving that result. You can post it here or use SQLFiddle or even PasteBin if there's too much data for SQLFiddle. – Vérace Feb 9 '15 at 11:36
  • Oooops - also give us SHOW CREATE TABLE alert_msg and some related data. – Vérace Feb 9 '15 at 12:31
1

From the question itself, I see two problems

  • The return value of the query will be, at most, one row. SQL_CALC_FOUND_ROWS is uesless
  • You are looking for the number of rows that piled up in the GROUP_CONCAT result

You have to extrapolate the row count from the result of the group_concat. Here is how

  • If there is a return value from the GROUP_CONCAT, set @found_rows = 1 + the number of commas in the return value.
  • If no value is returned for the GROUP_CONCAT, set @found_rows = 0

BTW there is a heads you need to be aware of: GROUP_CONCAT's max length is 1024 by default. You should extend it to 10M.

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(ca.id)
INTO @IDList
FROM notification AS ca 
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 ' 
AND  ca.date!="" 
AND  ca.alert_msg_id 
NOT IN(26,27) AND ca.from_id != '777'

SET @found_rows = IF(
    LOCATE(',',IFNULL(@IDList,''))=0,0,
    LENGTH(@IDList) - LENGTH(REPLACE(@IDList,',','')) + 1
);

SELECT @found_rows,@IDList;

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.