Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28

    Unanswered: Data retrieval question

    Hi I had a question 2 parts I got the first part.

    I have the following tables(just the keys for used for joining are mentioned)

    Sub Category SC_ID (PK)
    Class C_ID(PK), SC_ID (FK)
    Product P_ID(PK), C_ID (FK)
    UPC UPC_ID ,P_ID(FK)
    A product may have more than 1 UPC


    input value =UPC_id

    I have obtained SC_ID going upward
    (Now I know what sub cat, class and product the input UPC belongs to)

    Now I need to retrieve all UPCs under that Sub Category how do I do this
    thanks
    prester

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    given an input value for a UPC, you can actually get all the other UPCs in the same SubCategory in one query like this --
    Code:
    select SC.SC_ID     as subcategory
         , Cdown.C_ID   as classdown
         , Pdown.P_ID   as productdown
         , Udown.UPC_ID as UPCdown
      from UPC UPCup
    inner
      join Product Pup
        on UPCup.P_ID = Pup.P_ID
    inner
      join Class Cup
        on Pup.C_ID = Cup.C_ID
    inner      
      join SubCategory
        on Cup.SC_ID = SubcCategory.SC_ID
    inner
      join Class Cdown
        on SubcCategory.SC_ID = Cdown.SC_ID
    inner
      join Product Pup
        on Cdown.C_ID = Pdown.C_ID    
    inner
      join UPC UPCdown
        on Pdown.P_ID = UPCdown.P_ID
     where UPCup = 'inputvalue'
    rudy
    http://r937.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •