Postgresql – Get rows that are referenced in another table


I have two entities. product and bom.

CREATE TABLE product (
  id integer NOT NULL,
  CONSTRAINT product_pkey PRIMARY KEY (id)

/* Bill of materials*/
  id serial NOT NULL,
  parent_item_id integer NOT NULL,
  child_item_id integer NOT NULL,
  quantity integer NOT NULL

The entity product register single products and composite products (products made up of other products). The products that are composite, are referenced in the entity bom through parent_item_id and child_item_id.

I am using this structure because I can create composite products made of other composite products.

Now, What I want is to perform a SELECT that return all the products that are composed of another products.

More specifically, the records in the product table that are referenced in the table bom through and bom.parent_item_id.

So far I have the next SQL code:

FROM product
INNER JOIN bom ON = bom.parent_item_id;

My problem is that this JOIN give me duplicate product entries, obviously because appears in bom as many product is made of.

I want just one entry per product that is referenced in bom

Best Answer

Sounds as if you are looking for something like this:

FROM product
where exists (select *
              from bom 
              where bom.parent_item_id =;


FROM product
where id in (select parent_item_id
             from bom);