Query on Parent Child messages

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

SQL Server 2008 Forums

Transact-SQL (2008)

Query on Parent Child messages

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-03-29 : 04:35:15

Hi,

I have one table Messages which holds parent and its child messages.

Table:


mid  parentid  subject      msgbody
1      null      main         test
2        1       subreply      ..
3      null      test          ..
4        1       subreply      ..

So here, output should be like


mid    parentid    subject    msgbody   counts
4         1        subreply    ..         3
3        null      test        ..         0

I tried with query below for not exists but confused with count and fetching only latest record from it.


NOT EXISTS (SELECT 1 FROM tblMessages MInner WHERE MInner.ParentId = M.MessageId)

Please help me out on this query.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 04:55:58

how come the count is 3 for mid 4?
can you explain the logic?
why your output doesn't have MIDs( 1, 2)?

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-03-29 : 05:51:51

Hi,

In my output, message id 1 and 2 is not available because I want to display recent and latest message in my messages queue.

In my sample data message queue becomes for messageid 1. and total 3 counts for 3 messages which I want to display.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 06:22:50

[code]
--Your sample data script
DECLARE @Msgs TABLE(mid INT, parentid INT, subject varchar(30), msgbody varchar(30))
INSERT INTO @Msgs
SELECT 1, null, 'main', 'test' union all
SELECT 2, 1, 'subreply', 'afasj'union all
SELECT 3, null, 'test', 'afasj' union all
SELECT 4, 1, 'subreply', 'afasj'

SELECT mid, parentid, subject, msgbody
,case when parentid IS null then 0
else dense_Rank() over(order by case when parentid is null then 0 else mid end) end cnt
FROM @Msgs [/code]

Note: @Msgs sholud be your table name
EDIT: use TOP 2 clause based on mid descending for selecting latest messages

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 06:36:17

[code]SELECT TOP 2 mid, parentid, subject, msgbody
,case when parentid IS null then 0
else dense_RAnk() over(order by case when parentid is null then 0 else mid end) end cnt
FROM @Msgs
ORDER BY mid DESC
[/code]

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-03-29 : 06:57:59

Invalid output in case of below data..


DECLARE @Msgs TABLE(mid  INT, parentid INT, subject  varchar(30), msgbody varchar(30))
INSERT INTO @Msgs
SELECT 1, null, 'main', 'test' union all
SELECT 2, 1, 'subreply', 'afasj'union all
SELECT 3, null, 'test', 'afasj' union all
SELECT 4, 1, 'subreply', 'afasj' union all
SELECT 5, 1, 'subreply', 'afasjser'

SELECT TOP 2 mid, parentid, subject, msgbody
		,case when parentid IS null then 0 
			  else dense_RAnk() over(order by case when parentid is null then 0 else mid end) end cnt
FROM @Msgs 
ORDER BY mid DESC

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 07:33:39

what should be the output for above sample data?
i thought the count should be number of rows before that particular record...

Whats your logic behind the count?

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-03-30 : 01:20:51

Output is already given.

Suppose I have 3 Parent messsages and out of those three messages.. I have 5 child messages on first parent message.

So, Last child message of that parent and other two parent messages should be displayed.

In short, Most recent message of queue and if no child message then display parent message.


mid  parentid  subject      msgbody
1      null      main         test
2        1       subreply      ..
3      null      test          ..
4        1       subreply      ..
5        1       5subreply     ..
6      null      parent 6      ..
7        6       6thchild      ..

Output


mid    parentid    subject    msgbody   counts
5         1        5subreply    ..         4 (Recent message on queue)
3        null      test        ..          0 (No child messages)
7         6         6thchild   ..          2 (6th recent child message)

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-04-03 : 08:34:28

Any reply on this to make it done.. visakh or bandi?

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-04-03 : 12:05:00

not the best approach ,but try it

select
T1.mid
,T1.parentid
,T1.subject
,T1.msgbody
,coalesce(TC.NoCounts,0) as NoCounts
from YourTable T1
outer apply
(select top 1 coalesce(parentID,mID) as ID
,mID as mid
from YourTable as T2
where coalesce(T1.parentID,T1.mID)=coalesce(T2.parentID,T2.mID)
order by mid desc) as TR
outer apply
(select top 1 coalesce(parentID,mID) as ID
,count(coalesce(parentID,mID)) as NoCounts
from YourTable as TC
where coalesce(T1.parentID,T1.mID)=coalesce(TC.parentID,TC.mID)
group by coalesce(parentID,mID)
Having count(coalesce(parentID,mID))>1
) as TC
where t1.mid=tr.mid
order by coalesce(T1.parentID,T1.mID)

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources