simple my sql query problem

Question

0.00/5 (No votes)

See more:

Hello,

The below sql statement print user id, article id and notes. Now one user can have notes in different articles. I want to list all the notes under same articles together instead of repeating it.

For instance,

uid aid title notes
1 2 article1 test
1 2 article1 testing
1 3 article2 something else

So I want to display notes with article title -

Title
notes

i.e.
article 1
test
testing

article 2
something else

SQL

SELECT uid, aid, note
FROM notes_manager
WHERE uid = '123456'
ORDER BY dtime DESC

There must be an easy solution to this, just not striking me at the moment.
Thanks

Posted 11-Mar-13 2:34am

AndyInUK

Updated 11-Mar-13 18:41pm

gvprabu

v4

Add a Solution

Comments

Manfred Rudolf Bihy 11-Mar-13 9:04am

Not clear what you mean by not "repeating it". We have no idea what your UI looks like or what it is meant to look like. You'll have to explain a bit more what you are expecting.
Make clear from your SQL example what the expected output is supposed to be.

AndyInUK 11-Mar-13 11:23am

I have updated the question. Hope it makes sense now.

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)

gvprabu · Answer 1 · 2013-03-11T07:34:00

Hi,

Check the sample script...

SQL

-- User Define Function
CREATE FUNCTION [dbo].[fnSplitString](
    @Input VARCHAR(8000)					-- List of delimited items
  , @Delimiter VARCHAR(8000) = ','				-- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@Delimiter,@Input,0) <> 0
BEGIN
	SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@Input,1,CHARINDEX(@Delimiter,@Input,0)-1))),
	@Input=RTRIM(LTRIM(SUBSTRING(@Input,CHARINDEX(@Delimiter,@Input,0)+LEN(@Delimiter),LEN(@Input))))

	IF LEN(@sItem) > 0
		INSERT INTO @List SELECT @sItem
	END

	IF LEN(@Input) > 0
		INSERT INTO @List SELECT @Input -- Put the last item in
	RETURN
END

SQL

-- Sample Query
DECLARE @articleDtls TABLE(uid INT, aid INT, title VARCHAR(30), notes VARCHAR(100))
DECLARE @strArticle VARCHAR(MAX)

INSERT INTO @articleDtls (uid, aid, title, notes)
VALUES(1, 2, 'article1', 'test'),
(1, 2, 'article1', 'testing'),
(1, 3, 'article2', 'something')
SELECT uid, aid, title, notes FROM @articleDtls 


SELECT @strArticle=STUFF((SELECT  ','+A.title AS [text()]
FROM(SELECT TOP 100 PERCENT T.aid, T.title+(SELECT ','+ notes AS [text()] FROM @articleDtls WHERE aid=T.aid FOR XML PATH('')) 'title' 
	 FROM (SELECT DISTINCT aid, title FROM @articleDtls) T
	 ORDER BY T.aid) A
FOR XML PATH('')),1,1,'') 

SELECT Item FROM dbo.fnSplitString(@strArticle,',')

Regards,
GVPrabu

Prasad Khandekar · Answer 2 · 2013-03-11T04:43:00

Solution 1

Hello,

Does below SQL helps

SQL

SELECT uid, aid, notes FROM notes_manager ORDER BY uid, aid, dtime DESC

It will basically select article wise notes with latest note appearing first for all users. If you want to do this for a particular user then add WHERE clause.

Regards,

Posted 11-Mar-13 4:43am

Prasad Khandekar

Comments

AndyInUK 11-Mar-13 11:22am

I think I didn't explained the question well - I want to display result like -

article 1
test
testing

article 2
something else

With the above query, I end up repeating title for each notes under same article

Prasad Khandekar 11-Mar-13 22:52pm

Hello,

There are two things. One in selecting data in proper order & second is to display that in a particular way. The suggested query will help you retrieve the data in desired order. For display purpose you can use any hierarchical datagrid control. Have a look at custom controls from infragistics.