CODEWITHSUNDEEP
sqlpostgresql
John Paul
John Paul

Reputation: 5

Create an SQL query from two tables in postgresql

I have two tables as shown in the image. I want to create a SQL query in postgresql to get the pkey and minimum count for each unique 'pkey' in table 1 where 'name1' is not present in the array of column 'name' in table 2. 'name' is a array enter image description here

Upvotes: 0

Views: 56

Answers (1)

McNets
McNets

Reputation: 10807

You can use ANY to check if one element exists in your name's array.

create table t1 (pkey int, cnt int);
create table t2 (pkey int, name text[]);

insert into t1 values (1, 11),(1, 9),(2, 14),(2, 15),(3, 21),(3,16);
insert into t2 values
(1, array['name1','name2']),
(1, array['name3','name2']),
(2, array['name4','name1']),
(2, array['name5','name2']),
(3, array['name2','name3']),
(3, array['name4','name5']);

select pkey
from   t2
where 'name1' = any(name);

| pkey |
| ---: |
|    1 |
|    2 |

select   t1.pkey, min(cnt) count
from     t1
where    not exists (select 1
                     from   t2
                     where  t2.pkey = t1.pkey
                     and    'name1' = any(name))
group by t1.pkey;

pkey | count
---: | ----:
   3 |    16

dbfiddle here

Upvotes: 1

Related Questions