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*/
CREATE TABLE bom (
  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 product.id and bom.parent_item_id.

So far I have the next SQL code:

SELECT product.id
FROM product
INNER JOIN bom ON product.id = bom.parent_item_id;


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

I want just one entry per product that is referenced in bom
Sounds as if you are looking for something like this:

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


Alternatively

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