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..
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