Create a stored procedure called Task36 to display the Product Name and the Category
Name of all products and categories where the CategoryID’s of the Products table and the
DepartmentID’s of the Category table match. (Hint: you are required to use a type of JOIN
to retrieve this output).
what i wrote when i was trying to figure it out was along the lines of:
CREATE PROCEDURE Task36
AS
SELECT ProductName,categoryname
From Products JOIN Category
Final result is.
ON Products.departmentID = Category.departmentID
ALTER PROCEDURE [dbo].[Task36]
AS
SELECT Productname,CategoryName
FROM Products INNER JOIN Category
ON Products.CategoryID = Category.DepartmentID
With joins all we done was one basic exercise and i was thinking that for a join the field had to be in both areas while most people would see it as a simple thing i found it pretty frustrating , maybe you were thinking of more advanced joins.
While that may have technically solved the problem, whoever designed that database should be sent back to school to learn about naming conventions.
CategoryID implies that it is the FK (foreign key) which points to the PK (primary key) on the Category table. “Best Practices” (god, I hate that term!) implies that the name of the field on the Category table should be something like ID or even CategoryID. Definitely not DepartmentID, which implies it is the FK to the Department table primary key
Linking a FK on one table to what looks to be a FK on another table makes it nearly impossible for someone else to follow later to know how to join the tables without digging into the table structures. Ugh!
