If the user has no active records, read the inactive records using MYSQL-CodePudding

 id    userid    marks  status
  1      100      50      active
  2      100      55      inactive
  3      101      60      active
  4      102      70      inactive

I'm writing a single query to read users with the status "inactive." status only .

SELECT id, userid, marks
from user
where status = 'inactive'

Desired output is

   4      102      70      inactive

  Only the inactive records for this user.

Users are retried with active status also as per my query. if someone can assist me. I appreciate you.

CodePudding user response：

Try this:

SELECT id, userid, marks 
FROM user 
WHERE userid NOT IN (
  SELECT userid 
  FROM user 
  WHERE status = 'active'
  GROUP BY userid
)