Select statement with cross tables

  • Hi all, im new to sql and need some help with this problem.

    I have 3 tables as follows...

    Categories

    idCategories

    idParentCategory

    Catalog_id

    name

    Categories_x_products

    id_categories_x_products

    categories_id

    product_id

    Products

    id_products

    I am trying to create a sql statement so I can pull up all the products that are being pointed to by the categories table by specifing the name of the category. I am using the cross table since I want to be able to have multible products in each category and products in more then one category. Is this the right approch and what would the sql be to select the products I want

    Thanks in advance

    -Keith

  • Not knowing your entity definitions, or relationships, from what you have described it looks ok.

     

    Declare @category varchar(50)

    Set @Category = 'MyCategory'

    select id_Products

    From Products

    Join Categories_x_products on id_products = product_id

    join Categories on categories_id = idCategories

    where Categories.Name = @category

  • Thank you thank you.

    Now I understand by seeing your example. A big problem is how I named the columns which I though were consistant like product vs products and category vs categories and the _ in some of the id names but not others. I think thats where most of my troubles were coming from.

    I just tested the query and its doing exactly what I wanted

    thx tons

    -Keith

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply