CODEWITHSUNDEEP
sql
Vishal
Vishal

Reputation: 320

Display record of 1 column comparing results of 2 column

I have provided with the table details and what is my expected output:

Actual value would be

I need to know whether the user is Blocked, Active, Inactive. User can be in only 1 among all these 3 status.

For example, if user is blocked, he will not be active - So overall I need only 1 status. How can I write a SQL query for this case?

Upvotes: 0

Views: 69

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

Use CASE statement

SELECT p.FirstName,p.LastName,
       CASE WHEN t.IsBlocked=1 THEN 'Blocked'
            WHEN r.IsActive =1 THEN 'Active'
            ELSE 'Inactive' END as user_status
FROM User t JOIN Person p ON t.PersonId=p.Id
                     JOIN Resume r ON t.PersonId=r.PersonId

Upvotes: 1

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

In your case Person is the master table which contains all the people so you should left join the user and resume tables to that and use a case statement to get the status,

 SELECT P.ID,P.FirstName,p.LastName,case when T.IsBlocked=1 then 'Blocked'
               when R.IsActive =1 then 'Active'
               else  'Inactive' end as user_status
  FROM  Person P 
  LEFT JOIN User U on P.ID=U.PersonId
  LEFT JOIN Resume R on P.Id=R.PersonId

Upvotes: 1

Related Questions