updated on Fashion

IF NULL on join then change relation on left join

I will show part of query as it very long

from 
        (
            select `$Outer`.`id2` as `id2`,
                `$Outer`.`payment_id2` as `payment_id2`,
                `$Inner`.`payment_id` as `payment_id`,
                `$Inner`.`product_id` as `product_id`,
                `$Inner`.`package_id` as `package_id` //<-- If this is NULL Change Join Part


            from 
            (
                select `_`.`id2` as `id2`,
                    `_`.`id` as `id`,
                    `_`.`payment_id` as `payment_id2`
                from 
                (
                    select `$Outer`.`id2`,
                        `$Outer`.`id`,
                        `$Outer`.`payment_id`
                    from 
                    (
                        select `id` as `id2`,
                            `id` as `id`,
                            `payment_id` as `payment_id`
                        from `web`.`payments` `$Table`
                    ) `$Outer`
                    left outer join `web`.`orders` `$Inner` on (`$Outer`.`id` = `$Inner`.`id`)
                ) `_`
            ) `$Outer`
            left outer join 
                (
                    select `id`,
                        `payment_id`,
                        `product_id`,
                        `package_id`
                    from `web`.`products` `$Table`
                ) `$Inner` 
            on (`$Outer`.`id2` = `$Inner`.`payment_id`) //<--  Change this Join Part

        ) `$Outer`

So how to write right syntax?

if package_id is null

then

`$Inner` on (`$Outer`.`payment_id` = `$Inner`.`payment_id`)

instead of

`$Inner` on (`$Outer`.`id2` = `$Inner`.`payment_id`)

Recommended Articles