1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to add a product in multiple categories?

Discussion in 'C#' started by digitalunix, Jun 28, 2008.

  1. #1
    Hi.

    I would love to be very grateful if someone could help me. I have now 3 tables.

    .tbl_cat
    .tbl_sub
    .tbl_product

    tbl_cat
    cat_id|cat_name
    49|Comps
    50|Toys

    tbl_sub
    sub_id|cat_id|sub_name
    1|49|Notebook
    2|49|Accessories
    3|50|CaptainJack

    tbl_product
    id|cat_id|sub_id|prod_name|prod_specs
    1|49|1|Asus Notebook|Contentofnotebook
    2|49|1|Dell Notebook|Contentofnotebook
    ---

    When i call the products from sub categorie, i use the following SQL String:

    "select * from tbl_product a, tbl_sub b, tbl_cat c where a.CAT_ID = c.CAT_ID and a.SUB_ID = b.SUB_ID and a.CAT_ID = 49 and a.SUB_ID = 1 order by prod_name asc"

    now i want a new record in products which would be both in the Notebook subdir, and accessories subdir. Can anyone help me please?

    My mind is pretty confused.
    SEMrush
     
    digitalunix, Jun 28, 2008 IP
    SEMrush
  2. Social.Network

    Social.Network Member

    Messages:
    519
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    35
    #2
    I hope I understood your question, but it appears that you want to relate products to many subcategories. If so, the current design may not work for you. A product has a 1-to-1 relationship to both category and subcategory, what you need is a 1-to-many design. There are several options here, but one option is a relationship table such as:

    productsubcategory (table)
    --------------------------
    productsubcategoryrecid
    productrecid (id from tbl_product)
    subcategoryrecid (sub_id from tbl_sub)
    etc ...

    I hope this helps, if not we can expand on it. Let me know.
     
    Social.Network, Jun 28, 2008 IP
  3. digitalunix

    digitalunix Peon

    Messages:
    14
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thank you, so far everything has been done. but i am stuck in another point.
    Now my tables are like
    ----------------------------------------
    tbl_cat
    cat_id|cat_name
    ----------------------------------------
    tbl_sub
    sub_id|sub_name
    ----------------------------------------
    tbl_product
    id|prod_name
    ----------------------------------------
    tbl_prodcats
    id|prod_id|cat_id|sub_id
    ----------------------------------------

    can you please me write an (asp) sql statement which will show me the records of cat_name="test"?

    i use at the moment this:
    select * from tbl_product a, tbl_sub b, tbl_cat c, tbl_prodcats d where d.CAT_ID = c.CAT_ID and d.prod_id=a.id and d.SUB_ID = b.SUB_ID and (CAT_NAME like '%test%' or SUB_NAME like '%test%')

    if i have two categories like "Testing1" and "Testing2". Then this query shows two records from the product i want. I cannot group them, because if i group the cat_name should also be grouped and that will not solve my problem. Thanks in advance.
     
    digitalunix, Jun 30, 2008 IP
  4. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #4
    if you are a starter you better use a couple of SQLs and dont do joints

    first SQL would be
    second would be
    this would be a better solution for you
     
    ludwig, Jun 30, 2008 IP