Reterving Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reterving Rows

Author

Topic

sqlr2
Starting Member

4 Posts

Posted - 2014-03-25 : 02:52:49

Hi,

I have 2 tables "PRODUCTS" and "CATEGORIES".
Products
ProID ProName
1 A
2 B
3 C
4 D
5 E

Categories
ProID CatID CatName
1 1 AA
1 2 BB
1 3 CC
1 4 DD
2 3 EE
2 4 FF
3 1 GG
4 1 HH
4 3 II
4 4 JJ
5 1 KK
5 2 LL

I want to write a query, which returns only the starting 2 categories, when productID is Passed as a parameter.

If ProID=1
Output Example
1 1 AA
1 2 BB
If ProID=4
Output Example
ProID CatID CatName
4 1 HH
4 3 II

Thanks

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-25 : 03:13:50

DECLARE @Products TABLE(ProID INT,ProName CHAR(2))
INSERT INTO @Products VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')

DECLARE @Categories TABLE(ProID INT,CatID INT,CatName CHAR(4))
INSERT INTO @Categories VALUES(1,1,'AA'),(1,2,'BB'),(1,3,'CC'),(1,4,'DD'),
(2,3,'EE'),(2,4,'FF'),(3,1,'GG'),(4,1,'HH'),(4,3,'II'),(4,4,'JJ'),(5,1,'KK'),(5,2,'LL')

DECLARE @ProId INT
SET @ProId = 1
SELECT C.ProID,C.CatID,C.CatName FROM @Products AS P
INNER JOIN @Categories AS C
ON P.ProID = C.ProID
WHERE P.ProID = @ProId

Veera

calvinfoo
Posting Yak Master

129 Posts

Posted - 2014-03-25 : 03:16:38

Please go study about how to use INNER JOIN

sqlr2
Starting Member

4 Posts

Posted - 2014-03-25 : 03:38:18

quote:
Originally posted by VeeranjaneyuluAnnapureddy

DECLARE @Products TABLE(ProID INT,ProName CHAR(2))
INSERT INTO @Products VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')

DECLARE @Categories TABLE(ProID INT,CatID INT,CatName CHAR(4))
INSERT INTO @Categories VALUES(1,1,'AA'),(1,2,'BB'),(1,3,'CC'),(1,4,'DD'),
(2,3,'EE'),(2,4,'FF'),(3,1,'GG'),(4,1,'HH'),(4,3,'II'),(4,4,'JJ'),(5,1,'KK'),(5,2,'LL')

DECLARE @ProId INT
SET @ProId = 1
SELECT C.ProID,C.CatID,C.CatName FROM @Products AS P
INNER JOIN @Categories AS C
ON P.ProID = C.ProID
WHERE P.ProID = @ProId

Veera

Create Proc SP_CatDetails
@ProID int
as
Begin
Select P.ProID,C.CatID,C.CatName from Products P
INNER JOIN Categories C
ON P.ProID=C.ProID
Where P.ProID=@ProID
and C.CatID Between 1 and 2
order by C.CatID
End

I tired with the above query, but i know it gives only the details of CatID 1 and 2,
Where as all the products does not have CatID 1 and 2 so it will not work out for me.

I have Many Products and Categories so i cant use DECLARE nOW

Suggest me some other way.

Thanks

stpn
Starting Member

8 Posts

Posted - 2014-03-26 : 04:09:02

Can anybody look into this topic and help me in getting the solution...

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources