sql join query not working correctly

See more:

SQL

USE [cms]
GO
/****** Object:  StoredProcedure [dbo].[SpGetRelatedProducts]    Script Date: 10/10/2012 23:55:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SpGetRelatedProducts](@pid int)
AS
select *
from product join subcategory on subcategory.subcate_id=category.cate_id
where product.subcate_id in (
                              select subcate_id 
                              from product 
                              where pid = @pid
                            )

i want that all the products, which belong from different subcategory and these subcategories belongs from same category and also the columns of the subcategory table, thats why i used above join, but there is something wrong in the query.