mysql query with case and then counting the same query giving errors

Question

I don't know how to put this.

I have a query:

SELECT * , (

CASE 
WHEN t.video_id IS NOT NULL 
THEN pv.status
ELSE 4 
END
) AS video_status
FROM  `battles`  `t` 
LEFT JOIN process_videos pv ON ( pv.video_id = t.video_id ) 
WHERE title IS NOT NULL 
AND cat_id IS NOT NULL 
AND is_deleted =0
AND is_hidden =0
AND battle_type =  "public"
HAVING video_status
IN ( 3, 4 ) 
ORDER BY  `t`.`video_id` DESC 
LIMIT 0 , 30

my query is working fine, though when I add model->count() function it turn my query to this.

SELECT COUNT(*) FROM (SELECT *, ( CASE 
WHEN t.video_id IS NOT NULL 
THEN pv.status
ELSE 4 
END
) AS video_status FROM `battles` t LEFT JOIN process_videos pv on (pv.video_id = t.video_id) WHERE t.title IS NOT NULL AND t.cat_id IS NOT NULL AND t.is_deleted=0 AND t.is_hidden=0 and t.battle_type="public" HAVING video_status IN ( 3, 4 )) sq

Now this is creating problem. I am getting error from mysql : first i got "#1248 - Every derived table must have its own alias", then on updating query I added 't' alias to the columns which are mentioned in the query. Now I am having "Duplicate column name ''"

I saw such problem here. But can there be a better way, so that I don't need to add alias name myself.

There are more queries /filters added to this one but I have added the basic query here. Possibly need solution in mysql as well as in yii. I am using Yii 1.x

Adding more description. In Yii:

$criteria->select  = "*, ( CASE 
            WHEN t.video_id IS NOT NULL 
            THEN pv.status
            ELSE 4 
            END
            ) AS video_status";
        $criteria->join = "LEFT JOIN process_videos pv on (pv.video_id = t.video_id)";
        $criteria->order = $orderBy;
        $criteria->alias = 't';
        $criteria->condition = "t.title IS NOT NULL AND t.cat_id IS NOT NULL AND t.is_deleted=0 AND t.is_hidden=0 and battle_type='public'".$condition1; // and pv.status=3
        $criteria->having = " video_status IN ( 3, 4 )";
        if(!$count){
            $criteria->limit = 10
            $criteria->offset = 0
            $model = battles::model()->resetScope()->findAll($criteria);
        }
        else
        {
            $model_count = battles::model()->resetScope()->count($criteria);
            return $model_count;
        }

My both conditions run one by one. When I need to have count of battles. It shows me error in mysql while without count the same query runs correctly.

Modified my question: Added yii model query. @scaisEdge "video_status IN ( 3, 4 ) " is only working using HAVING Clause. It is showing error if I use it with in where clause. Error "Unknown column 'video_status' in 'where clause'"

score 1 · Answer 1 · answered Oct 23 '15 at 18:07

1

Having is for aggregate function. I don't see aggregate finction in your first select .... case ..

try with where clause this way

 SELECT * , (
   CASE 
   WHEN t.video_id IS NOT NULL 
     THEN pv.status
     ELSE 4 
   END
   ) AS video_status
   FROM  `battles`  `t` 
   LEFT JOIN process_videos pv ON ( pv.video_id = t.video_id ) 
   WHERE title IS NOT NULL 
   AND cat_id IS NOT NULL 
   AND is_deleted =0
   AND is_hidden =0
   AND battle_type =  "public"
   AND  video_status IN ( 3, 4 ) 
   ORDER BY  `t`.`video_id` DESC 
   LIMIT 0 , 30;

answered Oct 23 '15 at 18:07

ScaisEdge

131,976
10
91
107

I have problem with my 2nd query. First one is working fine, though I'll try your suggestion as well. 2nd query is giving errors. Can you please suggest? – Curious Developer Oct 26 '15 at 04:28
they are written in my question above. – Curious Developer Oct 26 '15 at 07:21
Have you already try` $criteria->having = " t.video_status IN ( 3, 4 )";` ? – ScaisEdge Oct 26 '15 at 11:51
I figured it out. I added all battle table column using `t.columnName` in query and it worked. And ` $criteria->having = "video_status IN ( 3, 4 )";` work without adding `t.` If we create alias column in query and want to fetch records according to it, we have to use HAVING clause instead of using it in WHERE clause. Now my queries are working fine. Thanks – Curious Developer Oct 27 '15 at 09:43
No your suggestion is not correct and not useful for me. Sorry and I am not the one you voted for it. – Curious Developer Oct 27 '15 at 12:09

mysql query with case and then counting the same query giving errors

1 Answers1