select query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

select query

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-08 : 02:25:19

Hi Experts

Please help this query I have one table LP_Mail

LP_Mail
MailId,ParentId,FromUserId,ToUserId,Subject,Text,Flag

1 0 2 3 Regardingdaily NULL 1
2 0 2 3 Regardingdaily NULL 1
3 1 3 2 Regardingdaily NULL 1
4 1 2 3 Regardingdaily NULL 1
5 0 2 89 Asp.net price 1
6 5 89 2 Asp.net 20$ 0

output

Subject Flag
Regarding daily updates(0) OUT
Regarding daily updates(0) OUT
Asp.net(1) IN

This is my test query

SELECT M.*,M.Subject+' ('+CAST((select count(*) from LP_MAIL where ParentId=M.MailId) AS VARCHAR(128))+')' AS SubjectText,
(CASE WHEN (select Flag from LP_mail where ParentId=M.MailId)=0 THEN 'IN' WHEN (select Flag from LP_mail where ParentId=M.MailId)=1 THEN 'OUT' END)
FROM LP_Mail M WHERE M.FromUserId=2 and M.ParentId=0

We got the error

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Thanks advance

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-08 : 05:12:40

try like this
SELECT M.*,M.Subject+' ('+CAST((select count(*) from @LP_MAIL where ParentId=M.MailId) AS VARCHAR(128))+')' AS SubjectText,
CASE WHEN m.Flag=1 THEN 'IN' ELSE 'OUT' END
FROM @LP_Mail M
INNER JOIN @LP_mail p ON p.parentid = m.mailid
WHERE M.FromUserId=2 and M.ParentId=0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources