How to apply variable specific condition in query's WHERE clause in Stored Procedure-CodePudding

Please tell me what I am doing wrong in below procedure query

CREATE PROCEDURE `DB`.`getReportsTotal`(customerId int(11),reportType varchar(20)) 
    BEGIN
        SELECT 
            `table1`.`column1`,
            `table2`.`column2`,
            reportType as `reportType`
        FROM table1
        LEFT JOIN table2
        ON table1.id = table2.table1_id 
        WHERE table2.customer_id = customerId 
        IF (reportType = "school")  
        AND `table1`.`column2` != "value";
END

I want, if reportType = "school" then only AND table1.column2 != "value"; this condition will apply. but this query giving mysql syntax error.

I also tried below query

CREATE PROCEDURE `DB`.`getReportsTotal`(customerId int(11),reportType varchar(20)) 
    BEGIN
        SELECT 
            `table1`.`column1`,
            `table2`.`column2`,
            reportType as `reportType`
        FROM table1
        LEFT JOIN table2
        ON table1.id = table2.table1_id         
        IF (reportType = "school") WHERE table2.customer_id = customerId AND `table1`.`column2` != "value";
        ELSE WHERE table2.customer_id = customerId ;
END

but getting the mysql error

Please help...

CodePudding user response：

You cpuld use following logic.

if it is not school use "value" 1 so that the "value" 1 != "value" is always true

If it is school you check against the column2

    SELECT 
        `table1`.`column1`,
        `table2`.`column2`,
        reportType as `reportType`
    FROM table1
    LEFT JOIN table2
    ON table1.id = table2.table1_id 
    WHERE table2.customer_id = customerId AND 
    IF (reportType = "school",`table1`.`column2`,"value" 1)  != "value";