'Compare two columns in PostgreSQL and conditionally select the rows

I have following postgres table.

entry_id  generic_name     uniprot_acc  stable_identifier  synonym          uniprot
D09574    ziv-aflibercept  A0A024RD33   D99937             ziv-aflibercept  P49765
D09574    ziv-aflibercept  A0A024RD33   D99937             ziv-aflibercept  P49763
D09574    ziv-aflibercept  A0A024RD33   D99937             ziv-aflibercept  P15692
D09574    ziv-aflibercept  A0A024RD33   D99937             ziv-aflibercept  P49765
D09574    ziv-aflibercept  A0A024RD33   D99937             ziv-aflibercept  P49763
D09574    ziv-aflibercept  A0A024RD33   D99937             ziv-aflibercept  P15692
D09574    ziv-aflibercept  P49763       D99937             ziv-aflibercept  P49765
D09574    ziv-aflibercept  A0A024RD33   D99937             ziv-aflibercept  P49765
D09574    ziv-aflibercept  A0A024RD33   D99937             ziv-aflibercept  P49763
D09574    ziv-aflibercept  A0A024RD33   D99937             ziv-aflibercept  P15692
D09574    ziv-aflibercept  Q86TW6       D99937             ziv-aflibercept  P49765
D09574    ziv-aflibercept  Q86TW6       D99937             ziv-aflibercept  P49763
D09574    ziv-aflibercept  Q86TW6       D99937             ziv-aflibercept  P15692
D09574    ziv-aflibercept  Q7LAP4       D99937             ziv-aflibercept  P49765
D09999    ziv              Q86TW6       D99936             ziv              P49763
D09999    ziv              Q86TW7       D99936             ziv              Q86TW6
D09999    ziv              Q86TW8       D99936             ziv              Q86TW7

Is there a way to compare values in columns uniprot_acc against uniprot column. If uniprot_acc value exist in uniprot, then exclude else include entry_id, generic_name, uniprot_acc.

The expected output is:

D09574  ziv-aflibercept A0A024RD33
D09574  ziv-aflibercept Q86TW6
D09574  ziv-aflibercept Q7LAP4
D09999  ziv             Q86TW8

D09574 ziv-aflibercept P49763 is excluded because 'P49763' exists in uniprot column.

Any help is highly appreciated.

postgresql


Solution 1:[1]

I believe this will do what you want:

SELECT entry_id, generic_name, uniprot_acc
  FROM table
EXCEPT
SELECT entry_id, generic_name, uniprot
  FROM table;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Kombajn zbo?owy

Related Questions