[RESOLVED] I want to change the view of the query.
Hi all
Please look the table query.
SELECT [Post_ID]
,[Post_Category_ID]
,[Post_Title]
,[Post_Description]
,[Is_Email_Notification]
,[Attachment_URL]
,[User_ID]
,[Is_Delete]
,[Is_Move]
,[Is_Close]
,[Total_Views]
FROM [Cfamla].[dbo].[Post_Detail]
SELECT [Post_Reply_ID]
,[Last_Modification_Date]
,[Post_ID]
,[Reply_Title]
,[Reply_Description]
,[Is_Email_Notification]
,[Attachment_URL]
,[User_ID]
,[Is_Delete]
FROM [Cfamla].[dbo].[Post_Reply]
Above are two table I want to data in the next format!
[Post_ID] ,[Post_Title]
[Post_Reply_ID],[Reply_Title]
mean In the first Row of the select statement it will show me the Post_Id that is always
After the next row... It will so all the reply of that post.
Hope I explain the question correctly.
Thanks
Re: I want to change the view of the query.
That needs to be done with a UNION ALL.
I'm going to assume that the Post_Id and Post_Reply_Id are "related" - being the same value - right?
Code:
Select Post_Id, Post_Title, 0
From Post_Detail
Union All
Select Post_Reply_Id, Reply_Title, 1
From Post_Reply
Order by 1,3
You need the 3rd column just to keep things in order.
Re: I want to change the view of the query.
The two tables can be linked using the Post_Id table.
Select PD.Post_Id, PD.Post_Title, PR.Post_Reply_Id, PR.Post_Detail
From Post_Detail PD Inner Join Post_Reply PR On PR.Post_Id = PD.Post_Id
Quote:
mean In the first Row of the select statement it will show me the Post_Id that is always
After the next row... It will so all the reply of that post.
I am not sure I fully understand what you mean.
Re: I want to change the view of the query.
I interpretted this
Quote:
[Post_ID] ,[Post_Title]
[Post_Reply_ID],[Reply_Title]
to mean that two rows would be returned for each post_id - first row the post_title and second row the reply_title. And so on and so on...
Re: I want to change the view of the query.
Right, that makes sense.
But if there can be multiple replies, ie
[Post_ID] ,[Post_Title]
[Post_Reply_ID],[Reply_Title]
[Post_Reply_ID],[Reply_Title]
[Post_Reply_ID],[Reply_Title]
[Post_ID] ,[Post_Title]
[Post_Reply_ID],[Reply_Title]
[Post_ID] ,[Post_Title]
[Post_Reply_ID],[Reply_Title]
[Post_Reply_ID],[Reply_Title]
Would a Union still work without including the Post_Reply.Post_Id field in the selection list.
Re: I want to change the view of the query.
Quote:
Originally Posted by szlamany
I'm going to assume that the Post_Id and Post_Reply_Id are "related" - being the same value - right?
oops - I did not see that POST_ID was in the reply table as well.
I guess it would be more like
Code:
Select Post_Id, Post_Title, Post_Id, 0
From Post_Detail
Union All
Select Post_Reply_Id, Reply_Title, Post_Id, 1
From Post_Reply
Order by 3,4,1
I added Post_Id as the 3rd column in each query - ordering by that column first - then the 0/1 column - and then I added an order by the first column in case dup Post_Reply_Id's need to be ordered.
Re: I want to change the view of the query.
Thanks for the reply, but I think you not get the question.
Here is the example
Post_Detail Table
Code:
Post_ID Post_Title
1 Basic Problem
[Post_Reply] Table
Code:
[Post_Reply_ID] [Post_ID], [Reply_Title]
11 1 This is the solution of the problem
12 1 I got it. Thanks
13 1 I have no Idea
If we use inner join then it will show only three record!
But I want record in the following format! for post Id=1
Code:
[ID] [Title]
1 Basic Problem
11 This is the solution of the problem
12 I got it. Thanks
13 I have no Idea
Hope you got the exact problem
Thanks
Re: I want to change the view of the query.
Did you try my query from post #6?
It returns those two columns of information exactly as you proposed.
But in order to keep the "order straight" you have no choice but to create bogus columns for sorting on.
Try it please.
Re: I want to change the view of the query.
