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
Best Answer
Sounds as if you are looking for something like this:
Alternatively