Need to only get one id

  • Hello all,

    Frequent flyer here...

    Here is my delima. I am in need to get the ProductGroupID from the product table; however, i am having a time tring to inner join. I can not INNER JOIN on the CompanyID because i haver more than one companyid related to different products,, I can not INNER JOIN on the ProductGroupID because there is many ProductGroupIDS... How can i join these two tables and only Pull the ProductGroupID (Products related to the prodcutgroupid only)

    I tried to make a link  between the company and the product table but the constraints are not letting me do that. How can i do this?

     

    Erik??...

    =============================Sproc...........................................

    ALTER PROCEDURE

    GetBlindCompanys

    @IDDepartment

    Int

    AS

    SELECT

    Company.CompanyID, Company.CompanyName, Company.CompanyShortDescription,

    Company.CompanyPhoto, Company.DepartmentID,

    Product.ProductGroupID

    FROM

    Company

    INNER JOIN Product on

    Company.CompanyID =

    Product.CompanyID

    WHERE

    Company.DepartmentID = @IDDepartment

    Dam again!

  • These two tables are already link by company.companyID

    So is it possiable to have another link link.. company.ProductgroupID = Product.ProductGroupID???...

    Dam again!

  • Erik - could you please post the DDLs of your Company & Product table ?!

    Reg. "is it possible to have another link" - sounds like you're talking about a composite key and yes - you can define an index that is made up of one or more columns!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Thank you for the reply,

    I had my first composit key delima yesterday. I found myself in a situation that i needed to INNER JOIN ON  something else other than PK_ of my Company table. What threw me a curve is that when i was working in the designer (Enterprise Manager) i (for some reason) thought that after creating the second index on the company table and having a FK_ on the Product Table that another link should have been visiable to see with the eye. So, i spent my day yesterday all alone with sql2000, just me and sql2000 all alone singing the blues; however, we got to know each other a little better and it worked out for the best.. HA,HA..Kinda, like that bully in school that you always wanted to beat up, and some how wound up in the same room with him and found out that he was just a big softy..

    OH Yea, No more taking off on the weekends...

     

    Here is the link that better shows what i am talking about..

     

    http://afcc1.com/sqlHELP 

    Dam again!

  • Here is the Spcro that i was working with, I am not sure why i posted that other one, i think my mind was racing..

     

    ALTER PROCEDURE

    Load_Menu_For_Companys

    @IDCompany

    int,

    @IDProductgroup

    int

    AS

    SELECT

    Product.ProductID , Product.ProductName, Product.CompanyProductCategoryID, Company.CompanyID, Company.CompanyName, Company.CompanyLogo, Product.CompanyProductCategoryName

    FROM

    Company INNER JOIN

    Product ON Company.CompanyID = Product.CompanyID

    WHERE

    Company.CompanyID = @IDCompany

    and Product

    .ProductgroupID = @IDProductgroup

    Dam again!

  •  

    Hello,

    I think a composite key is a likely bet. However I also noticed that you are not completely qualifying your filter criteria. try adding "Product.CompanyID = @IDCompany" to the where clause and it will run much faster:

     

    WHERE Company.CompanyID = @IDCompany

    and Product.CompanyID = @IDCompany

    and Product.ProductgroupID = @IDProductgroup

    Best wishes,

    Barry O'Connell

  • I am not sure what you mean. They both look the same to me.

     

    Where are you talking about?

    Erik...

    Dam again!

Viewing 7 posts - 1 through 6 (of 6 total)

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