How to search teachers name in the database either the status is 0 or 1? I dont know if this is correct "Status"<=1
SELECT * FROM "Teachers" WHERE LOWER("Name") = LOWER($1) AND "Section"=$2 AND "Status"<=1 LIMIT $3 OFFSET $4'
CodePudding user response:
Your sql is okay but it will selected records if you have status like -1, -2 as @Luuk states. My recommendation to use IN for readability and select only status you specify
SELECT * FROM "Teachers" WHERE LOWER("Name") = LOWER($1) AND "Section"=$2 AND "Status" IN (0,1) LIMIT $3 OFFSET $4
CodePudding user response:
In MySQL the boolean values are usually stored in TINYINT(1) format. In your case, to retrieve the teacher where their status is either 0(false) or 1(true) the SQL query would be:
select * from Teachers where LOWER("Name") = LOWER($1) AND "Section" = $2 and "Status" = 0 or "Status" = 1 LIMIT $3 OFFSET $4
One more thing you should try to follow, try to retrieve the relevant columns from the database rather than all the columns, it is a lot more performant than getting all the columns. Assuming the Teachers table has the following columns:
- Id
- FirstName
- LastName
- FullName
- Salary
- Designation
- Status
- Section etc.
And, suppose you only want to get the FirstName, LastName, Designation, Section from the table. You should run the following query,
select FirstName,LastName,Designation,Section from Teachers where LOWER("Name") = LOWER($1) AND "Section" = $2 and "Status" = 0 or "Status" = 1 LIMIT $3 OFFSET $4
Hope this helps in learning SQL and best of luck!