How does this code gonna work??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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]

kellywong911
Starting Member

3 Posts

Posted - 2013-12-06 : 04:51:17

WOW that helps a lot!!! thanks very very very much

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

kellywong911
Starting Member

3 Posts

Posted - 2013-12-06 : 05:04:21

u guys are really awesome i can't thank you enough

Kristen
Test

22859 Posts

Posted - 2013-12-06 : 06:43:09

Is that more efficient Peter?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources