Problem getting specific record

Question

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 14-May-13 23:26pm

Arslan Elahi

Updated 14-May-13 23:45pm

Maciej Los

v3

Add a Solution

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....

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-05-15T00:10:00

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)[^]

Basmeh Awad · Answer 2 · 2013-05-15T00:19:00

Solution 3

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)

Posted 15-May-13 0:19am

Basmeh Awad