October 27, 2005 at 2:19 am
I have a s_p that retrieves data from the post table
Bu i want to have a subquery between for example:-
SELECT TOP 20 postid
,subject ,
name ,
replies ,
views ,
dt,
flag,
(Select top 1 replyid,name,dt from Reply where postid =@postid) as test
FROM
post
WHERE
postid <@vm
ORDER BY postid DESC
I get error "Only one expression can be specified in the select list when the subquery is not intoduced with EXISTS
ANy ideas??
October 27, 2005 at 2:24 am
sub query should return only one value. You sub query return 3 column rather than 1.
change to :
SELECT TOP 20 postid
,subject ,
name ,
replies ,
views ,
dt,
flag,
(Select top 1 replyid from Reply where postid =@postid) as test_replyid,
(Select top 1 name from Reply where postid =@postid) as test_name,
(Select top 1 dt from Reply where postid =@postid) as test_dt
FROM
post
WHERE
postid <@vm
ORDER BY postid DESC
October 27, 2005 at 2:34 am
A subselect in the SELECt clause can only return 1 field. Join it is as a subquery.
Do you want the latest reply for a post? (assuming so for the below query)
SELECT TOP 20 postid, subject,
name, replies, views, dt, flag, Reply.ReplyID, Reply.Name, Reply.dt
FROM Post INNER JOIN Reply ON Post.PostID = Reply.PostID INNER JOIN (SELECT PostID, MAX(dt) LatestDate FROM Reply GROUP BY PostID) LatestReply ON Reply.PostID = LatestReply.PostID AND Reply.dt = LatestReply.LatestDate
WHERE PostID <@vm
ORDER bY PostId dESC
Done this way, SQL only has to read the reply table once for the query, instead of once per row of post.
Not tested, but should be right.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2005 at 5:10 am
GilaMonster thhx for the post and yes i want to retrieve the LAST REPLY of each post in a datagrid
but when i tried your query in Query analyzer i got Ambigoius error name postid,subject etc..
October 27, 2005 at 5:17 am
SELECT TOP 20 Post .postid, subject, name, replies, views, dt, flag, Reply.ReplyID,
Reply.Name, Reply.dt
FROM Post
INNER JOIN Reply ON( Post.PostID = Reply.PostID)
INNER JOIN( SELECT PostID, MAX(dt) AS LatestDate
FROM Reply
GROUP BY PostID) LatestReply
ON( Reply.PostID = LatestReply.PostID
AND Reply.dt = LatestReply.LatestDate)
WHERE Post.PostID < @VM
ORDER BY Post .PostId DESC
I wasn't born stupid - I had to study.
October 27, 2005 at 5:18 am
Specify the table for all the columns. I didn't know what your tables looked like, so I too a guess. My bad.
SELECT TOP 20 Post.postid, Post.subject, Post.name, Post.replies, Post.views, Post.dt, Post.flag, Reply.ReplyID,
Reply.Name, Reply.dt FROM ....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2005 at 6:38 pm
Thx very much for the reply and the S_p u posted did the trick
Bu i have one more problem since i'm using a datagrid for it after binding it to the Grid i get duplicate postid's
Which means i have let say postid=1 twice whcih i don't want because its showing the same subject twice on the Datagrid.
Is there a way i can distinct the postid's
Although i changed the code to use LEFT JOIN because the users need to see other subject posts if there is no reply which will just be Null or i just add a dash(-) showing no reply to that post..
Any ideas..
October 27, 2005 at 6:38 pm
Thx very much for the reply and the S_p u posted did the trick
Bu i have one more problem since i'm using a datagrid for it after binding it to the Grid i get duplicate postid's
Which means i have let say postid=1 twice whcih i don't want because its showing the same subject twice on the Datagrid.
Is there a way i can distinct the postid's
Although i changed the code to use LEFT JOIN because the users need to see other subject posts if there is no reply which will just be Null or i just add a dash(-) showing no reply to that post..
Any ideas..
October 27, 2005 at 6:48 pm
Are you getting this because of multiple replies, (names, dates, etc...)? If that is the case, you can concatenate these with a function, but it depends upon how you want to display the results...
I wasn't born stupid - I had to study.
October 27, 2005 at 10:58 pm
Farrell thanks for the reply.Yes when there is more that one reply its a problem because after databinding it to a datagrid and i load the newpost page the some subjects apper twice on the scree if they have 2 replies becos the postid's aren't unique.I need the SQl statement to retrieve uniques postid's..
Why using the function u mentioned how can i use that?
October 28, 2005 at 12:06 am
The way I wrote that query it should only return one reply per post, unless you have replies with identical dates.
Can you please post some example data and your table structures so we can take a look and come up with a working solution?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2005 at 4:54 am
I have method I have worked wit help from people on this site that concatenates multiple values based upon a similar ID. Unfortunately, I have that code at work. Once I get in, I can copy some tables with data and the function used to concatenate multiple records associated with a particular ID, (I was thinking of submitting an article based upon this process).
You will need to think about display of this situation, (i.e., put the data and name with the reply text as one concatenated field...). I would also question your need to display the PostID. This may confuse the Client as it may have no real meaning to them since it is a PK and internal to your program, but has no other meaning to the Client. This kind of information is usuallly best to retain in a non displayed column of the grid, (if that is possible) of holding within a variable set of records).
I will post my sample tables, data, function, and outcome for you unless someone has the time to post this information beforehand. You will need to learn the method and apply it to your situation.
It basically is a simple function that concatenates multiple fields into one column for display. This will involve deciding how you want to display many replies to one PostID through word wrapping or whatever approach you decide upon - you can always add a retrun character, [ CHAR(10) OR CHAR(13) ] to force a line feed.
Once I am into work, I can look for this and post it...
Again, I would seriously reconsider displaying PostID to the Client as this may cause you more difficullty than you want as the common user does not understand how those values are used and what they actually mean, especially if those fields are Identity Columns...
I wasn't born stupid - I had to study.
October 29, 2005 at 12:49 am
Thx guys for the replies..
GilaMonster its very well possible that i have similar reply dates and actually didn't notice that.But i the postid' are ok now after using max(replyid) and did a Group By.I would be waiting for the concatenating multiple values and also will post my DDL for Gila Monster to have a look.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply