Create an SQL query from two tables in postgresql

Question

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

Good question, but you'd make it easier for people by providing text, not images. Ideal would be CREATE TABLE and INSERT statements. — Laurenz Albe
– Laurenz Albe, Commented Feb 25, 2018 at 11:13

McNets · Accepted Answer · 2018-02-25 11:16:03Z

1

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

edited Feb 25, 2018 at 11:16

answered Feb 25, 2018 at 10:50

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create an SQL query from two tables in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related