SQL query to find out existince of a record with different source column values in other rows-CodePudding

I have a table with App name, server name and source of that record (3 unique sources). Table can contain same app name, server name but different source for any record, and also sometimes for an app it can be present in one source and not in other. Now I want to find out records for three scenarios. I am trying to use except but not working, can someone give me query for this.

Table

app	server	source
abc	123	A
abc	123	B
abc	123	C
def	456	A
def	456	B
ghi	789	A
jkl	012	B
jkl	012	C

Scenarios: server in

src A	src B	src C	Action
Yes	Yes	Yes	No action
Yes	No	No	add to B
Yes	Yes	No	add to C
No	Yes	Yes	remove from B and C

Expected Output: No need to display abc 123 as it is present in all 3 sources

app	server	action
def	456	add to C
ghi	789	add to B
jkl	012	remove from B and C

CodePudding user response：

You can group by app and server and proceed exactly like you describe:

SELECT app, server
       CASE
          WHEN     has_a AND     has_b AND     has_c
          THEN 'No action'
          WHEN     has_a AND NOT has_b AND NOT has_c
          THEN 'add to B'
          WHEN     has_a AND     has_b AND NOT has_c
          THEN 'add to C'
          WHEN NOT has_a AND     has_b AND     has_c
          THEN 'remove from B and C'
       END AS action
FROM (SELECT app, server,
             bool_or(source = 'A') AS has_a,
             bool_or(source = 'B') AS has_b,
             bool_or(source = 'C') AS has_c
      FROM tab
      GROUP BY app, server) AS grouped;

Note that action will be NULL for those cases not mentioned in your question.