CODEWITHSUNDEEP
mysqlsql
Sam Malayek
Sam Malayek

Reputation: 3765

Multiple WHERE clauses with a condition

I've looked at a bunch of similar stackoverflow questions but I haven't been able to figure this out.

                    WHERE `Table1`.`Column1` = 'criteria1'
                    AND `Table1`.`Column2` = 'criteria2'
                    AND `Table1`.`Column3` LIKE 'criteria3'
                    AND IF(EXISTS IN `Table2`, (`Table2`.`Delete` <> 2, `Table2`.`SectionId` IS NOT NULL), '')

I'm trying to execute a query with five WHERE clauses, where the last two are based on the condition that the row exists in the joined Table2. If the condition is false, throw in an empty string ''.

I've tried variations of the above code but it's incorrect syntax. Is what I'm trying to do possible?

Can I use an IF inside a WHERE clause like that?

EDIT (here's the full query...):

        $sql_query = "SELECT
                    `Courses`.`Id` AS CourseId,
                    IFNULL(`Courses`.`CourseName`, '') AS CourseName,
                    IF(`Courses`.`CourseNumber` > '', `Courses`.`CourseNumber`, -1) AS CourseNumber,
                    IFNULL(`Courses`.`CourseDepartment`, '') AS CourseDepartment,
                    IFNULL(`Courses`.`Notes`, '') AS CourseNotes,
                    IFNULL(`Courses`.`Year`, '') AS CourseYear,
                    IFNULL(`Courses`.`CourseType`, '') AS CourseType,
                    `Sections`.`Id` AS SectionId,
                    IFNULL(`Sections`.`SectionNumber`, '') AS SectionNumber,
                    IFNULL(`Sections`.`SectionName`, '') AS SectionName,

                    `StudentsCourses`.`CustomerId` AS CustomerId,

                    CONCAT(`Courses`.`Id`, '-', `Sections`.`Id`, '-', `StudentsCourses`.`FirstName`, `StudentsCourses`.`LastName`, '_', `StudentsCourses`.`TeacherEmail`) AS TempCustomerId,
                    IFNULL(`StudentsCourses`.`FirstName`, '') AS StudentFirstName,
                    IFNULL(`StudentsCourses`.`LastName`, '') AS StudentLastName,
                    IFNULL(`Customers`.`Email`, IFNULL(`StudentsCourses`.`StudentEmail`, '')) AS StudentEmail,
                    IFNULL(`StudentsCourses`.`TeacherFirstName`, '') AS TeacherFirstName,
                    IFNULL(`StudentsCourses`.`TeacherLastName`, '') AS TeacherLastName,
                    IF(`StudentsCourses`.`CustomerId` IS NOT NULL, `Customers`.`CustomerName`, CONCAT(`StudentsCourses`.`FirstName`, ' ', `StudentsCourses`.`LastName`)) AS FullName,
                    IF(`StudentsCourses`.`CustomerId` IS NOT NULL, CONCAT(REPLACE(`Customers`.`CustomerName`, ' ', ''), '_', `Customers`.`Email`), CONCAT(`StudentsCourses`.`FirstName`, `StudentsCourses`.`LastName`, '_', `StudentsCourses`.`TeacherEmail`)) AS NameKey,
                    `Customers`.`UserRoleId` AS UserRoleId,
                    `Customers`.`MagentoId` AS MagentoId,
                    `StudentsCourses`.`StudentId` AS StudentId,
                    `SiteProfile`.`StudentIdField` AS StudentIdField,
                    `SiteProfile`.`SchoolEmailDomain` AS SchoolEmailDomain,
                    `StudentsCourses`.`Id` AS StudentsCoursesId,
                    IFNULL(`Orders`.`Id`, '') AS OrderId
                FROM `Courses`
                    LEFT JOIN `StudentsCourses` ON `Courses`.`Id` = `StudentsCourses`.`CourseId`
                    LEFT JOIN `BooksCourses` ON `Courses`.`Id` = `BooksCourses`.`CourseId`
                    LEFT JOIN `Products` ON `BooksCourses`.`ISBN` = `Products`.`ISBN`
                    LEFT JOIN `EbookVendors` ON `Products`.`EbookVendorId` = `EbookVendors`.`Id`
                    LEFT JOIN `Sections` ON `Sections`.`Id` = `StudentsCourses`.`SectionId`
                    LEFT JOIN `Customers` ON `StudentsCourses`.`CustomerId` = `Customers`.`Id`
                    LEFT JOIN `SiteProfile` ON `Courses`.`SchoolCode` = `SiteProfile`.`SchoolCode`
                    LEFT JOIN `Orders` ON `Customers`.`Id` = `Orders`.`CustomerId`

                    WHERE `Courses`.`SchoolCode` = '{$criteria["school_code"]}'
                    AND `Courses`.`Year` = {$criteria["year"]}
                    AND `Courses`.`CourseType` LIKE '{$criteria["term"]}'
                    AND `StudentsCourses`.`Delete` <> 2
                    AND `StudentsCourses`.`SectionId` IS NOT NULL";

Upvotes: 0

Views: 74

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

It is not very clear exactly what you are asking, but I am guessing something like this condition might be what you are looking for:

AND (StudentCourses.CourseId IS NULL 
     OR (`StudentsCourses`.`Delete` <> 2
         AND `StudentsCourses`.`SectionId` IS NOT NULL
        )
    )

Edit: It would best if you edited your question to explain in plain language what you want the condition to do, as it is not apparent at all what the intent was from the syntax you originally presented.

Upvotes: 1

Related Questions