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)
 How does this code gonna work???

Author  Topic 

kellywong911
Starting Member

3 Posts

Posted - 2013-12-06 : 03:35:20
Since the tbl1 cant be recognized for inner join?
can anybody help thanks

select * from
(select ID from tbl_Message
WHERE ([From_User] = '38137F2C-591C-4BE6-91F8-F2C915F26066' OR [To_User] = '38137F2C-591C-4BE6-91F8-F2C915F26066')
AND [Replied_ID] = '00000000-0000-0000-0000-000000000000'
and deleted = 0 ) tbl1 inner join
(select top 1.Replied_ID as Reply_ID, Post_Date as Reply_Date from tbl_Message
where Replied_ID = tbl1.ID or ID = tbl1.ID
order by Post_Date desc) tbl2 on tbl2.Reply_ID =tbl1.ID

Kristen
Test

22859 Posts

Posted - 2013-12-06 : 03:56:29
[code]
SELECT ID,
Reply_ID,
Reply_Date
FROM
(
SELECT [T_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY M1.ID
ORDER BY M1.ID, M2.Post_Date DESC
),
M1.ID,
M2.Replied_ID as Reply_ID,
M2.Post_Date as Reply_Date
FROM tbl_Message AS M1
JOIN tbl_Message AS M2
ON M2.Replied_ID = M1.ID
OR M2.ID = M1.ID
WHERE (
M1.[From_User] = '38137F2C-591C-4BE6-91F8-F2C915F26066'
OR M1.[To_User] = '38137F2C-591C-4BE6-91F8-F2C915F26066'
)
AND M1.[Replied_ID] = '00000000-0000-0000-0000-000000000000'
AND M1.deleted = 0
) AS X
WHERE T_RowNumber = 1
[/code]
Go to Top of Page

kellywong911
Starting Member

3 Posts

Posted - 2013-12-06 : 04:51:17
WOW that helps a lot!!! thanks very very very much
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-06 : 04:59:42
[code]SELECT t1.ID,
t2.Reply_ID,
t2.Reply_Date
FROM (
SELECT ID
FROM dbo.tbl_Message
WHERE '38137F2C-591C-4BE6-91F8-F2C915F26066' IN ([From_User], [To_User])
AND [Replied_ID] = '00000000-0000-0000-0000-000000000000'
AND Deleted = 0
) AS t1
CROSS APPLY (
SELECT TOP(1) w.Replied_ID AS Reply_ID,
w.Post_Date AS Reply_Date
FROM dbo.tbl_Message AS w
WHERE w.Replied_ID = t1.ID
ORDER BY w.Post_Date desc
) AS t2;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

kellywong911
Starting Member

3 Posts

Posted - 2013-12-06 : 05:04:21
u guys are really awesome i can't thank you enough
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-06 : 06:43:09
Is that more efficient Peter?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-08 : 09:32:09
Could be. Depends on current index situation.
I've found CROSS APPLY to play really nice with parallellism.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -