I have two entities.
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 )
product register single products and composite products (products made up of other products). The products that are composite, are referenced in the entity
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
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