mysql if condition met then execute join otherwise not

Question

Hi I am stuck over a query. I want to execute join statement only when condition in if statement is true otherwise do not execute join and let the rest of query execute.

For example, my query is like this:

 SELECT `t`.`id`, `t`.`title`, `t`.`date_created`, `t`.`video_id`, `t`.`audio_id`, `t`.`created_by`, `t`.`updated`, `t`.`gamenumber`, `t`.`cat_id`, `t`.`is_deleted`, `t`.`marked_by`, `t`.`is_hidden`, `t`.`battle_type`, `t`.`media` FROM `postdata` `t`

if t.video_id != '' then run join statement

left join processes as pv on ( pv.video_id = t.video_id)

a common WHERE Statement in whole query

cat_id IS NOT NULL and is_deleted=0 and is_hidden=0 and battle_type="public" ORDER BY date_created

WHERE statement also requires this condition if t.video_id!='' then

pv.status=3

Basically if 'postdata' table contain field 'video_id' which contain id of videos. And 'processes' table contain these video_id as well. So if postdata.video_id is not empty then execute join statement to check if video_id is processes table contain status=3. (if status =3 only then fetch video records) otherwise if postdata.video_id is empty then execute the query without join statement and common Where Statement.

Please help.

why not just put AND t.video_id <> '' in the join predicate. you'll get null values for the rows that dont match. — pala_
– pala_, Commented May 27, 2015 at 13:34
@craafter, I have simplified the query in mysql. I am actually working on Yii and using CDbcriteria for executing this query. — Curious Developer
– Curious Developer, Commented May 29, 2015 at 2:16

cn0047 · Accepted Answer · 2015-05-27 13:41:48Z

0

I think you should use left join every time and add pv.status=3 to join conditions, like:

<?php

$where = [
    'is_deleted'  => 0,
    'is_hidden'   => 0,
    'battle_type' => 'public',
];
$postdata = new postdataModel;
$processes = new processesModel;
$command = $postdata->getDbConnection()->createCommand()
    ->select('t.id, t.title, t.date_created, t.video_id, t.audio_id, t.created_by, t.updated, t.gamenumber, t.cat_id, t.is_deleted, t.marked_by, t.is_hidden, t.battle_type, t.media')
    ->from('postdata AS t')
    ->leftJoin('processes AS pv', 'pv.video_id = t.video_id AND pv.status = 3')
    ->where('cat_id IS NOT NULL AND is_deleted = :is_deleted AND is_hidden = :is_hidden AND battle_type = :battle_type')
    ->order('t.date_created DESC')
;
$data = $command->queryAll();

and mysql will resolve all for you...

answered May 27, 2015 at 13:41

cn0047

17.2k7 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql if condition met then execute join otherwise not

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