implementing "spare parts"

• yogiberr

  Hall of Fame

  Points: 3679

  More actions

  January 13, 2004 at 9:43 am

  #152014

  Hi,

  I want to implement products/spare parts.

   

  I have the following schema

   

  <SCHEMA>

  tblProduct

  productId

  productCategroryId (refs tblProductCategory.categoryId)

  productName

  etc

   

  tblProductCategory

  categoryId

  categoryName

  isSparePart  (BIT)

  <\SCHEMA>

   

   

  Now,  when I am searching for spare parts, I can use a udf to simplify my queries..

   

  udfGetSpareParts

  SELECT DISTINCT

              p.productId,

              p.productName

  FROM

              tblProduct p,

              tblProductCategory pc

   

  WHERE p.categoryId = pc.categoryId

  AND pc.isSparePart= 1

   

  So, I can select against the table that this udf returns, instead of selecting directly against tblProduct.

   

  Does this look ok?

   

  I know that it is possible to implement a B.O.M but I feel that this is beyond me at the minute.

  I’d just really like to know if I am setting myself up for major heartache in the near future if I go with this method.

   

  Many thanks,

  Yogi,

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  January 13, 2004 at 10:47 am

  #489802

  I'd change to ANSI syntax, but it should work.

   

  SELECT DISTINCT

              p.productId,

              p.productName

  FROM

              tblProduct p inner join tblProductCategory pc

                 on p.productcategoryId = pc.categoryId

  where  pc.isSparePart= 1

• yogiberr

  Hall of Fame

  Points: 3679

  More actions

  January 13, 2004 at 11:07 am

  #489807

  

  Hi Steve,

  Righto, ANSI it is. 

  ta,

  yogi.