Click here to Skip to main content
12,955,170 members (58,925 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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.
Posted 10-Oct-12 9:21am
Comments
Zoltán Zörgő 10-Oct-12 15:36pm
   
Although I don't really got your intentions - I can not see, that you join the category table, but you use it in the join condition.
Rohit Shrivastava 10-Oct-12 18:46pm
   
There should be a reference of category table? Do you have one. Please provide the tables and relationships. Is input parameter product id?
damodara naidu betha 11-Oct-12 2:03am
   
Can you post what is the result coming and what should be the desired out put along with input data?

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

change
from product join subcategory on subcategory.subcate_id=category.cate_id

to
from product join subcategory on subcategory.subcate_id=product.subcate_id

and remove your where clause and replace it with:
where pid = @pid
  Permalink  
Comments
damodara naidu betha 15-Oct-12 2:35am
   
My 5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,559
CHill60 3,550
Maciej Los 3,153
ppolymorphe 2,030
Jochen Arndt 1,975


Advertise | Privacy | Mobile
Web01 | 2.8.170525.1 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100