0

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

0

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);
1
  • That's what I was looking for, thanks for the quick response. Oct 6 '16 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.