Why doesn't SQL query work without...?

Question

SQL

Actually there is a small table of categories. Purpose -> Display categories in DrowDownList based on Session["UserID"]. Added test data.
[img] http://ipic.su/img/img7/fs/ask1.1440547824.jpg[/img] + Request itself

/*
exec usp_Category_Select '2'
*/
ALTER Procedure [dbo].[usp_Category_Select](
       @UserID int	          
       ) as
select Category.CategoryID, Category.Category, Category.UserID, [User].UserID
from [User], [Category] 
where @UserID = Category.UserID and [User].UserID = @UserID

Actually the question is: Why if you compare "only" - where @UserID = Category.UserIDwithout Category.UserID and [User].UserID = @UserIDme it displays all categories with ID 1 and 2, renaming all this to ID = 1 or 2, depending on which ID I add to the parameter. After all, there is already a clogged id in the Category table, which I can compare with the id that is in the session, why doesn’t it work without a second comparison? It turns out that I get a table with two UserIDs. It's interesting just to understand this nuance :) And so everything already works fine. But -> through the poke method.

Answer 1

Hello.
The thing is that you combine the Users and Category tables with one condition, and with the second query you filter the record only by category relative to the user.
In your case, if you do not need other fields from the users table, you can remove this table from the query altogether.
That is, it will be like this.

/*
exec usp_Category_Select '2'
*/
ALTER Procedure [dbo].[usp_Category_Select](
       @UserID int	          
       ) as
SELECT Category.CategoryID, Category.Category, Category.UserID
FROM [Category] 
WHERE Category.UserID = @UserID

If you need an intersection query between two tables, then it is better to use INNER JOIN so that everything is clear and not confusing for you.

/*
exec usp_Category_Select '2'
*/
ALTER Procedure [dbo].[usp_Category_Select](
       @UserID int	          
       ) as
SELECT [Category].CategoryID, [Category].Category, [Category].UserID, [User].UserID
FROM [User]
INNER JOIN [Category] ON [User].UserID = [Category].UserID /* Это условие пересечения двух таблиц */
WHERE [Category].UserID = @UserID  /* Это условие фильтрации по полю UserID */