Home > Enterprise >  How to search in database all Status is 0 or 1?
How to search in database all Status is 0 or 1?

Time:10-24

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!

  • Related