Question 352112 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get a column that has custom values based on data in a table?

I have a couple of tables which have information about an activity(like an article), how do I get a column back that spits out a value based on some condition

Learner Certificate view dt
First 2023-10-09
Second 2024-01-03
Learner Certificate valid date
First 2023-07-09
Second 2024-02-23

For example I want to get some data through some comparisons(could be a different condition too) like

if(certificate view dt < certificate valid date) then return 'qualified' otherwise return 'unqualified'

ultimately I want to get something like this

Learner Status
First qualified
Second unqualified

How to achieve that in sql or do I need something like a PL/SQL ?

Tags:

2

Answers


  1. 0

    With CASE

    SELECT 
  Learner,
  CASE WHEN (certificate_view_dt < certificate_valid_date) 
    THEN 'qualified' 
    ELSE 'unqualified' 
  END AS status
FROM my_table

    see online demo

    -- create
CREATE TABLE my_table (
  learner VARCHAR(50),
  certificate_view_dt DATE,
  certificate_valid_date DATE
);

-- insert
INSERT INTO my_table (learner, certificate_view_dt, certificate_valid_date)
VALUES 
    ('First', '2023-10-09', '2023-07-09'),
    ('Second', '2024-01-03', '2024-02-23');


-- fetch 
SELECT 
  Learner,
  CASE WHEN (certificate_view_dt < certificate_valid_date) 
    THEN 'qualified' 
    ELSE 'unqualified' 
  END AS status
FROM my_table

    output

     learner |   status    
---------+-------------
 First   | unqualified
 Second  | qualified
    Login or Signup to reply.
  2. 0

    The answer provided by @SimoneNigro is correct, except your data being split into two tables (I cannot see why you would do this but I’ll assume you have a valid reason). Since you have two tables it is necessary to consolidate them into single tuple for each learner. You use join to accomplish that. Resulting query becomes: (see demo here)

    with learner_cert(learner, certificate_view_dt, certificate_valid_date) as
     ( select vi.learner,vi.certificate_view_dt,va.certificate_valid_date
         from certificate_view  vi
         left join certificate_valid  va
           on vi.learner = va.learner
     ) 
select learner
     , case when certificate_view_dt < certificate_valid_date
            then 'qualified'
            else 'unqualified'
       end status
  from learner_cert;

    NOTE: Demo adds learner Third to certificate_view but not to certificate_valid to show what happens with a missing row in one of the tables. Depending upon your data conditions the query may need corresponding adjustments.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search