[RESOLVED] I want to change the view of the query.

[shakti5385]

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

[szlamany]

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?

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.

[brucevde]

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

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.

[szlamany]

I interpretted this

[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...

[brucevde]

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.

[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

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.

[shakti5385]

Thanks for the reply, but I think you not get the question.
Here is the example

Post_Detail Table

Post_ID         Post_Title
1                  Basic Problem

[Post_Reply] Table

[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

[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

[szlamany]

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.

[shakti5385]

Thanks szlamany