How to pulls related values in PostgreSQL?

Here is my problem.

I have two tables like user and customer. Users can be related to some customer(s).

For example, I have a user who relates to two customers, and the other user relates to three customers, etc.

This is my code that creating tables and inserting values;

create table tbl_user
(
 id int,
 username varchar(100),
 relatedcustom_id int
)

 create table tbl_custom
(
 id int,
 name varchar(100)
)

 insert into tbl_custom values(1,'john'),
 (2,'adam'),
 (3,'steve'),
 (4,'marliyn'),
 (5,'coco'),
 (6,'George');

 insert into tbl_user values (1,'cst_moose',1),
 (1,'cst_moose',2),
 (2,'cst_moose',3),
 (3,'cst_kevin',2),
 (4,'cst_kevin',5),
 (5,'cst_donald',1),
 (6,'cst_donald',2),
 (7,'cst_donald',4),
 (8,'cst_henry',1),
 (9,'cst_henry',6),
 (10,'cst_michel',1),
 (11,'cst_michel',2);

I want to pull the user name(s) that relates to customers that id is 1 and 2.

Here is my select code;

  select username from tbl_user where exists (select 1 from tbl_custom 
  where 
  id in(1,2))
  except
  select username from tbl_user where exists (select 1 from tbl_custom 
  where 
  id not in(1,2))

But this query demonstrates nothing.

The other select code ;

select username from tbl_user where relatedcustom_id in (1,2)
except
select username from tbl_user where relatedcustom_id not in (1,2)

This query shows like this;

  username
  --------
  cst_michel

What I want to do is a Select – As – From with the following fields

   username
  --------
  cst_michel
  cst_moose
  cst_donald

Answer

One of the possible solution is to use arrays:

select username
from tbl_user
where relatedcustom_id in (1,2) -- to filter out unrelated data
group by username
having array[1,2] <@ array_agg(relatedcustom_id);
-- or '{1,2}' <@ array_agg(relatedcustom_id);

Advertisement

Answer