Zed Fang Zed Fang - 3 months ago 10
SQL Question

In postgresql, when the row exists, show the row and add an additional column. And always show one row even though it doesn't exist

I mean Postgresql specifically.

For example, I have a table as below:

client action date
A search 2016-08-22
A search 2016-08-21
A search 2016-08-20
B search 2016-06-22
B Ads 2016-07-10
B Ads 2016-08-20


I am looking each client separately. If I look for client 'A' and 'Ads' in action, I want to return a table like:

client action status last_date
A -- no existed --


If I look for client 'B' and 'Ads' in action,

client action status last_date
B Ads existed 2016-08-20


I tried
case when
, but it will return several rows until appearance of
action = 'Ads'


I tried
where action like 'Ads'
, but it won't return anything if there is no such action for a client.

I want to always show a row to visualize the data.

Answer
WITH cteCross AS(
    SELECT DISTINCT client, c.SearchText
    FROM
       TableName t
       CROSS JOIN (SELECT 'Ads' as SearchText) c
    WHERE t.client = 'A'
)

SELECT
    c.client
    ,t.action
    ,CASE WHEN t.client IS NOT NULL THEN 'existed' ELSE 'No Existed' END as status
    ,MAX(date) AS last_date
FROM
    cteCross c
    LEFT JOIN TableName t
    ON c.client = t.client
    AND c.SearchText = t.action
GROUP BY
    c.client
    ,t.action
    ,CASE WHEN t.client IS NOT NULL THEN 'existed' ELSE 'No Existed' END

You have to know what you do not know....

So to do that you have to first understand all of the possible combinations that could exist. That is why you take a cross join of the value you want to search for and the DISTINCT clients.

Next you join back to the original table to see what exists and what doesn't.

If you take out the WHERE in the cteCross it will give you results for all clients the way you want them.