CODEWITHSUNDEEP
sqlpostgresql
Alex
Alex

Reputation: 35

Compare two variables of two tables and get rows that in single row in both two variables have values that are in first table but not in second

I am interested in code that will work in PostgreSQL.

Lets create two tables:

create table tbl_1 (id bigserial, var_1 text, var_2 text, compare_1 text, compare_2 text);
insert into tbl_1 (var_1, var_2, compare_1, compare_2) values
  ('a', 'a', 1, 11),('a', 'a', 2, 22),('a', 'a', 4, 44),('a', 'a', 5, 55); 

create table tbl_2 (id bigserial, var_1 text, var_2 text, compare_1 text, compare_2 text);
insert into tbl_2 (var_1, var_2, compare_1, compare_2) values
  ('b', 'b', 1, 11),('b', 'b', 2, 22),('b', 'b', 4, 77),('a', 'a', 6, 66);

tbl_1 looks like:

id  var_1  var_2  compare_1  compare_2
1   "a"    "a"    "1"        "11"
2   "a"    "a"    "2"        "22"
3   "a"    "a"    "4"        "44"
4   "a"    "a"    "5"        "55"

tbl_2 looks like:

id  var_1  var_2  compare_1  compare_2
1   "b"    "b"    "1"        "11"
2   "b"    "b"    "2"        "22"
3   "b"    "b"    "4"        "77"
4   "a"    "a"    "6"        "66"

Now I need to get all rows of tbl_1 which both compare_1 and compare_2 variables values on the single row are not present in tbl_2 both compare_1 and compare_2 variables values on the single row. So, I want to get result that looks like:

id  var_1  var_2  compare_1  compare_2
3   "a"    "a"    "4"        "44"
4   "a"    "a"    "5"        "55"

Upvotes: 1

Views: 55

Answers (2)

forpas
forpas

Reputation: 164089

Use NOT IN:

SELECT * FROM tbl_1
WHERE (compare_1, compare_2) NOT IN (SELECT compare_1, compare_2 FROM tbl_2)

See the demo.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use not exists:

select t1.*
from tbl_1 t1 
where not exists (select 1
                  from tbl_2 t2
                  where t2.compare_1 = t1.compare_1 and
                        t2.compare_2 = t2.compare_2
                 );

Upvotes: 0

Related Questions