Click here to Skip to main content
15,071,729 members
Please Sign up or sign in to vote.
1.33/5 (2 votes)
See more:
Hi Experts,

i've a problem and that is : i've two tables

Category
Cat_Id(PK)        Cat_Name
   1                     abc
   2                     Kar
   3                     Isb


Items
Item_ID(PK)         Item_Name        Cat_Id(FK)
1                      Item1                 2
2                      Item2                 1
3                      Item3                 1
4                      Item4                 3
5                      Item5                 2


i want to display data from "Items" on the basis of Cat_Id(FK)..
For Example:

Category 1
Item2 
Item3

Category 2
Item1 
Item5 

Category 3
Item4


How can i do this in one Sql query/Procedure.... I've tried enough myself with different conditions and logics but in vain....

Please Help me..

Thanx in Advance...
Posted
Updated 14-May-13 23:45pm
v3
Comments
NarasimhaMurthy 15-May-13 5:50am
   
Do you want that data in Single column as in your example?
[no name] 15-May-13 5:56am
   
no.. i want to display that data on one Asp Page As like above
RelicV 15-May-13 5:57am
   
Check this format if this helps you or not.

Category | Items
-------------------|----------------
CATEGORY 1 | Item2, Item3
CATEGORY 2 | Item1, Item5
CATEGORY 3 | Item4

If so, then let me know.
[no name] 15-May-13 6:20am
   
Yes it'll defintly help me....

Try this:

SQL
DECLARE @cat TABLE (Cat_Id INT IDENTITY(1,1), Cat_Name NVARCHAR(30))

INSERT INTO @cat (Cat_Name)
VALUES('abc')
INSERT INTO @cat (Cat_Name)
VALUES('Kar')
INSERT INTO @cat (Cat_Name)
VALUES('Isb')

 
DECLARE @itm TABLE (Item_ID INT IDENTITY(1,1), Item_Name NVARCHAR(30), Cat_Id INT)

INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item1', 2)
INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item2', 1)
INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item3', 1)
INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item4', 3)
INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item5', 2)

SELECT c.Cat_Name, i.Item_Name
FROM @cat AS c RIGHT JOIN @itm AS i ON c.Cat_Id = i.Cat_Id
ORDER BY c.Cat_Name

;WITH allitems AS
(
	SELECT Cat_Id, '===' + Cat_Name + '===' AS [CategoriesAndItems], 0 As Item_Id
	FROM @cat
	UNION ALL
	SELECT Cat_Id, Item_Name AS [CategoriesAndItems], Item_Id
	FROM @itm
)
SELECT [CategoriesAndItems]
FROM allitems
ORDER BY Cat_id, Item_Id


SELECT statement returns:
abc	Item2
abc	Item3
Isb	Item4
Kar	Item5
Kar	Item1


CTE returns:
===abc===
Item2
Item3
===Kar===
Item1
Item5
===Isb===
Item4


More:
Using Common Table Expressions[^]
UNIONA (T-SQL)[^]
   
SQL
select c.Cat_Id,i.Item_Name  from items i inner join category c on c.Cat_Id=i.Cat_Id where c.Cat_Id in
 (select Cat_Id from category)
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900