Data retrieval question

macjoubert · #1 (**permalink**) 10-31-03, 17:11

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

r937 · #2 (**permalink**) 10-31-03, 18:44

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/

Internet Services

Web Development

Digital Marketing

Consumer Tech