Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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.
Posted 10-Oct-12 10:21am
Comments
Zoltán Zörgő at 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 at 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 at 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 at 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
0 OriginalGriff 304
1 Maciej Los 285
2 Sergey Alexandrovich Kryukov 230
3 Shweta N Mishra 186
4 Aajmot Sk 153
0 OriginalGriff 7,660
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,760


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2014
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