Select with inner join to retrieve field and its occurences

  1. akika

    akika Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Hi,
    can you pls with below select:
    Code (SQL):
      SELECT PRDT.PRODUCT_REF,
             PRDT.PRODUCT_DESC
          FROM PRODUCT_TBL PRDT
          INNER JOIN Product_code_tbl PRDCD
          ON PRDT.Product_Ref = PRDCD.Product_Ref
          INNER JOIN Product_cat_tbl PRDCAT
          ON PRDT.Product_Ref = PRDCAT.Product_Ref
          WHERE
          PRDCD.code_id = 'NEW';
    Main table is Product_tbl having sub tables product_code_tbl, product_category_tbl.
    The sub tables are linked by the field product_ref and have a sequence number field.

    can u pls help how to get the equivalent prdt_code_ref and product_cat_id description are available from main table product_tbl where code_id = NEW

    The output display:
    product_ref|product_desc|product_desc for first prdt_code_ref from product_code_tbl ^ product_desc for second prdt_code_ref from product_code_tbl|product_desc for first product_cat_id from product_category_tbl
    65954|CLOTHING STORE|SHOES AND BAGS^ELECTRONICS|ACCESSORIES

    ^ seperate the occurences and pipe the fields.

    Attached some sample data & tbl struct. can i use a single select or multiple select?

    Pls help on how to build the select
     

    Attached Files:

    Last edited: Oct 15, 2019