Is there a way to select a "dynamic" column based on LEFT JOIN?-CodePudding

I have a query that looks like this:

SELECT
   cid,
   COALESCE(newcust.newcust, false) AS newcust,
   COALESCE(svipstat.svipstat, false) AS svipstat,
   COALESCE(vipstat.vipstat, false) AS vipstat
FROM customer c
LEFT JOIN (
    SELECT cid, true AS newcust
    FROM customer c
    WHERE active IS TRUE AND created_at >= current_date - interval '1 day' * 30
) AS newcust ON newcust.cid = c.cid
LEFT JOIN (
    SELECT cid, true AS svipstat
    FROM customer c
    WHERE active IS TRUE AND (select count(1) from visits where cid = customer.cid and vdate >= current_date - interval '30 days') >= (SELECT value FROM criteria WHERE c = 'svipstat' AND criteria.pid = c.pid)
) AS svipstat ON svipstat.cid = c.cid
LEFT JOIN (
    SELECT cid, true AS vipstat
    FROM customer c
    WHERE active IS TRUE AND (select count(1) from visits where cid = customer.cid and vdate >= current_date - interval '30 days') >= (SELECT value FROM criteria WHERE c = 'vipstat' AND criteria.pid = c.pid)
) AS vipstat ON vip.cid = c.cid;

What this query returns, is something like this:

cid | newcust |  svip | vipstat
--------------------------------
1.     true.     false.    false
2.    false.    true.      false
3.    false.    false.     true

My ideal scenario, would be if I can have a column return a "string", based on the LEFT JOINS I have, for example:

cid | status
-------------
1.    newcust
2.    svip
3     vipstat

Is there a way to achieve this??? I've been having a hard time trying to find a solution :(

CodePudding user response：

You can just use the CASE WHEN logic to choose the status value in the outer query like this

SELECT
    cid,
    CASE WHEN COALESCE(newcust.newcust, false) 
         THEN 'newcust'
         WHEN COALESCE(svipstat.svipstat, false) 
         THEN 'svipstat'
         WHEN COALESCE(vipstat.vipstat, false)
         THEN 'vipstat'
    END status
FROM ...